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

Could you upload the file here please? I may have an answer for you.

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.

Attached Files

Position_in_rows_of_data.xlsx (18.2 KB, 1 views)

In AD2:copy across one column and down.

I get NAME error, What's 'TOCOL', sorry?

TOCOL converts array to a single column.
While trying to find something more concise, try:

a bit shorter:

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.

Attached Files

Position_in_rows_of_data.xlsx (23.4 KB, 1 views)

In the attached, see formula at cell AI2:
Copy down. It will spill to the right. I feel sure it could be more elegant.

Note that I used my shorter formula in msg#22 in cells AB2:AC25; it's more robust.

Attached Files

msofficeforums51391Position_in_rows_of_data.xlsx (24.2 KB, 1 views)

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?

Can you try to work out which functions are giving the problem? (type the function name after the equals sign in a blank cell and see if the intellisense suggestions include that function, then tell me which functions you don't have)


Maybe, we'll come to that later. Why?

No problem, 'rws' '_xlfn' and VSTACK.

This should deal with VSTACK:can you show a screenshot of the formula with _xlfn in it?
if LET is OK then rws should be too (it's only a variable).

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.

Wrap the existing formula in:
=INDEX(theExistingFormula,,{1,2,3,4,6,7,17})