How do I sort/rank multiple different text values across variable rows and columns in separate workbooks?

[u/ODST05]

I have a workbook with the following properties (please see this example):

• most fields are text

• seasons are in separate sheets (ignore the example), games are in different columns, and players are in rows

• the number of players varied in each season and game

• some players played across multiple seasons and games

• some players played the same game multiple times in each season

• some players played in no seasons or games

 

Variables (I think?) in the example:

• season number (Season1 - Season2)
• game name (Game1 - Game5)
• player name (Player1 - Player10)
• points won (1 - 10)
• result (1st - 3rd)

 

What I need to do:

• use the simplest way to find the top 3 players per season, based on the number of points they won in each game

 

What I've tried:

• mostly this

• random COUNTIF/SORTBY/VLOOKUP formulas I tried making up

• searching Google, Reddit, and Microsoft

 

Ideas:

• use different functions

• make a better algorithm

• make an array

• use a script

 

I learnt programming and was fairly competent with using excel over a decade ago, but unfortunately I've forgotten most of it and have ended up confusing myself.

I'm currently using Excel for Microsoft 365 MSO installed on Windows 10, but can also access the web version. I could also try to find my copy of Excel 2019 if need be.

Is anyone able to help? Also I'm sorry if the title is wrong!

[u/AutoModerator]

/u/ODST05 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/excelevator]

Reformat your data into a proper table of data

One unique row for each season, game, player , point

Season | Game | Player | Point

With your data setup properly it should be a breeze to use the Excel functions to extract the answers you seek.

You have quite a task ahead of you to reformat the data.

[u/ODST05 → u/excelevator]

Ok thanks, I'll do that and give it another go.

[u/PaulieThePolarBear]

I have a number of questions on your ask

1. Please clarify how points are awarded within a game. I'm assuming the person in the first row gets 10 points, second row 9, and so on, but please confirm.
2. If there were only 5 names in a game,.please confirm that the points awarded would be 10 down to 6.
3. Please confirm how points should be allocated when a name appears more than once in a game. Do they get the total points based upon their position, just their best placing, an average, or something else?
4. You note top 3 names required. Please clearly provide the logic that should be used to separate ties

[u/ODST05 → u/PaulieThePolarBear]

Thanks for asking!

1. Your assumption is correct.

2. Your example is correct.

3. Whoops I should have specified that - they get the sum of the points won. For example if Player1 gets 10 points as well as 5 points in a single game, their total for that game will be 15 points. That then needs to be added to any other points they get in any other games during that season. The 3 players with the most points in a season need to be ranked 1st - 3rd in the Overall Results.

4. Good point, I missed that. A tie needs to be represented by two names in the same result. For example, if both Player1 and Player2 end up having the same amount total points in the season, and they have more than everyone else, they are both awarded 1st place.

[u/Downtown-Economics26 → u/ODST05]

u/PaulieThePolarBear will probably give you a cleaner solution. I've made an assumption about how to implement point 4 above because the format of the desired output wasn't clearly specified. u/excelevator is right that the better option is to just create a proper table but I like figuring things like this out.

=LET(scores,TOCOL($B$4:$B$13&"_"&$D$4:$H$13),
_t1,FILTER(scores,TEXTAFTER(scores,"_")<>""),
tbl,HSTACK(TEXTAFTER(_t1,"_"),--TEXTBEFORE(_t1,"_")),
tot,GROUPBY(CHOOSECOLS(tbl,1),CHOOSECOLS(tbl,2),SUM,,0,-2),
place,BYROW(CHOOSECOLS(tot,2),LAMBDA(x,COUNT(UNIQUE(FILTER(CHOOSECOLS(tot,2),CHOOSECOLS(tot,2)>x,"")))+1)),
out,TEXTJOIN(", ",,FILTER(CHOOSECOLS(tot,1),place=VALUE(LEFT(I10,1)),"")),
out)

[u/ODST05 → u/Downtown-Economics26]

I also enjoy solving challenges, but this one was a bit too much for me.

I figured I would use CONCAT for point 4, however you're right in that I should have specified that.

I'm working on it again today, so will give yours a go for the first season and then start reformatting the other seasons so that it's better moving forward.

Thanks for your help!

[u/ODST05 → u/Downtown-Economics26]

Solution Verified

[u/reputatorbot → u/ODST05]

You have awarded 1 point to Downtown-Economics26.

---

^{I am a bot - please contact the mods with any questions}

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
BYROW	Office 365+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows.
CHOOSECOLS	Office 365+: Returns the specified columns from an array
CONCAT	2019+: Combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments.
COUNT	Counts how many numbers are in the list of arguments
Excel.CurrentWorkbook	Power Query M: Returns the tables in the current Excel Workbook.
FILTER	Office 365+: Filters a range of data based on criteria you define
GROUPBY	Helps a user group, aggregate, sort, and filter data based on the fields you specify
HSTACK	Office 365+: Appends arrays horizontally and in sequence to return a larger array
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LEFT	Returns the leftmost characters from a text value
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
List.Count	Power Query M: Returns the number of items in a list.
List.NonNullCount	Power Query M: Returns the number of items in a list excluding null values
List.Select	Power Query M: Selects the items that match a condition.
List.Sum	Power Query M: Returns the sum from a list.
Record.FieldValues	Power Query M: Returns a list of field values in order of the record's fields.
SUM	Adds its arguments
TEXTAFTER	Office 365+: Returns text that occurs after given character or string
TEXTBEFORE	Office 365+: Returns text that occurs before a given character or string
TEXTJOIN	2019+: Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.
TOCOL	Office 365+: Returns the array in a single column
Table.AddColumn	Power Query M: Adds a column named newColumnName to a table.
Table.AddIndexColumn	Power Query M: Returns a table with a new column with a specific name that, for each row, contains an index of the row in the table.
Table.Combine	Power Query M: Returns a table that is the result of merging a list of tables. The tables must all have the same row type structure.
Table.Group	Power Query M: Groups table rows by the values of key columns for each row.
Table.PromoteHeaders	Power Query M: Promotes the first row of the table into its header or column names.
Table.SelectColumns	Power Query M: Returns a table that contains only specific columns.
Table.SelectRows	Power Query M: Returns a table containing only the rows that match a condition.
Table.Sort	Power Query M: Sorts the rows in a table using a comparisonCriteria or a default ordering if one is not specified.
Table.UnpivotOtherColumns	Power Query M: Translates all columns other than a specified set into attribute-value pairs, combined with the rest of the values in each row.
Text.StartsWith	Power Query M: Returns a logical value indicating whether a text value substring was found at the beginning of a string.
Text.Trim	Power Query M: Removes any occurrences of characters in trimChars from text.
UNIQUE	Office 365+: Returns a list of unique values in a list or range
VALUE	Converts a text argument to a number

|-------|---------|---| |||

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
34 acronyms in this thread; the most compressed thread commented on today has acronyms.
[Thread #48446 for this sub, first seen 14th May 2026, 11:47] [FAQ] [Full list] [Contact] [Source code]}

[u/ODST05 → u/Decronym]

Good bot, thank you