#16
|
|||
|
|||
Hi there,
Would you be able to share the solution you found to this problem. I've been looking to solve this problem for weeks myself. Would like to figure home and away teams' league position ahead of their match but haven't been able to. More details can be found on this thread regarding the problem. League Position in Rows of data |
#17
|
||||
|
||||
Could you upload the file here please? I may have an answer for you.
|
#18
|
|||
|
|||
Hello
As you may know in a 4 team group teams should be ranked from 1 to 4 before every match (or matchday) since there are only 4 teams in the group. Their positions may change after every match based on the results they get. I thought this could be achieved by IF function and setting criteria such as Matchday (in column D) and Group (Column G) to distiguish AB:AC values based on those criterias [ IF((D3 = $D$2:$D$28981) * (G3 = $G$2:$G$28981) ]. I input the expected results in columns AD:AE for better understanding for people who aren't into football. |
#19
|
||||
|
||||
In AD2:
Code:
=MATCH(Z2,SORT(TOCOL(FILTER($Z$2:$AA$97,($D$2:$D$97=$D2)*($G$2:$G$97=$G2))),,-1),0) |
#20
|
|||
|
|||
I get NAME error, What's 'TOCOL', sorry?
|
#21
|
||||
|
||||
TOCOL converts array to a single column.
While trying to find something more concise, try: Code:
=MATCH(ROUND(Z2,9),ROUND(SORT(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,FILTER($Z$2:$AA$97,($D$2:$D$97=$D2)*($G$2:$G$97=$G2)))&"</b></a>","//b"),,-1),9),0) |
#22
|
||||
|
||||
a bit shorter:
Code:
=MATCH(ROUND(Z2,9),ROUND(SORT(VALUE(TEXTSPLIT(TEXTJOIN("¬",TRUE,FILTER($Z$2:$AA$97,($D$2:$D$97=$D2)*($G$2:$G$97=$G2))),,"¬")),,-1),9),0) |
#23
|
|||
|
|||
Mate, you're a genious. Looked all over the web for weeks. Chatgtp took a whole week and was just going in circles. AI has long to go before it can reach people your level. There's no way I would have figured this with that thing. Only a programmer could have pulled this off.
I have another problem to solve for you. I think it can be done using MATCH function and don't think it's that complicated (for you that is). So, I also wanted the total points (columns O:P) of different positions (1 to 4) to be displayed in ROWS of columns (AD:AG), this way I would be able to calculate the distance between different positions before each matchday later on. I have input the expected results on columns AD:AG. Feel free to change column headings to 1 to 4 and use them as reference cells if needed. Hope I explained myself well. Hopefully I'm not asking for much. |
#24
|
||||
|
||||
In the attached, see formula at cell AI2:
Code:
=LET(rws,($D$2:$D$97=$D2)*($G$2:$G$97=$G2),TAKE(SORT(VSTACK(VALUE(TEXTSPLIT(TEXTJOIN("¬",TRUE,FILTER($N$2:$O$97,rws)),"¬")),VALUE(TEXTSPLIT(TEXTJOIN("¬",TRUE,FILTER($Z$2:$AA$97,rws)),"¬"))),2,-1,TRUE),1)) Note that I used my shorter formula in msg#22 in cells AB2:AC25; it's more robust. |
#25
|
|||
|
|||
Thanks for the help mate. I'm getting NAME error again when I drag them down. I suspect lots of those functions are built-in VBAs/Vstacks that aren't recognised by my excel or am I talking non-sense? Also, is it possible not to have it Spilled and rather have a seperate formula for each cell?
|
#26
|
||||
|
||||
Quote:
Maybe, we'll come to that later. Why? |
#27
|
|||
|
|||
No problem, 'rws' '_xlfn' and VSTACK.
|
#28
|
||||
|
||||
This should deal with VSTACK:
Code:
=LET(rws,($D$2:$D$97=$D2)*($G$2:$G$97=$G2),TAKE(SORT(CHOOSE({1;2},VALUE(TEXTSPLIT(TEXTJOIN("¬",TRUE,FILTER($N$2:$O$97,rws)),"¬")),VALUE(TEXTSPLIT(TEXTJOIN("¬",TRUE,FILTER($Z$2:$AA$97,rws)),"¬"))),2,-1,TRUE),1)) if LET is OK then rws should be too (it's only a variable). |
#29
|
|||
|
|||
It works fine when there are 4 teams in the group but for my other spreadsheets there can be 18 to 20 teams in a group/league and I don't want it spilling across 20 cells. Besides I would like to know how you reference those cells/positions so I could pick and choose the positions I prefer. For example positions 1,2, 3, 4, 6, 7 and 17 are important in a 20 team league because they determine the Winner, European places and Relegation zone whereas the rest are kind of pointless to have so it would be a waste of space to have them all.
|
#30
|
||||
|
||||
Quote:
=INDEX(theExistingFormula,,{1,2,3,4,6,7,17}) |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Cross-reference with full context a numbered list inside another multilevel list (list style) | MatLcq | Word | 0 | 02-01-2021 06:00 AM |
Calculate a Date | shawn.low@cox.net | Mail Merge | 5 | 12-12-2019 03:22 PM |
are there any good free golf league programs out there | kener40 | Other Software | 0 | 03-28-2014 05:54 PM |
calculate age | userman | Excel | 8 | 06-02-2012 10:59 PM |
Calculate formula base of list menu | rkeles | Excel | 4 | 09-22-2010 12:38 AM |