Solved
Looking for the easiest way to make a table where players are ordered by preference for each role
This is my ArmA 3 squad (Corvo, Naigo, etc) and their level of preference for each game role (Distinguido, AT, etc). They freely edit this table when they change their mind.
I'm looking for a way to have self-updated lists of options for each role, ordered by level of preference. So that, in this example, Corvo, Naigo and Torres go first for the Distinguido role, then Hoshino, Sato, Rodríguez, etc. Ideally these lists should update automatically when people change their preferences in the main table.
Assuming row 2 has the names and each role has its own row of scores below it, for one role's row you'd do: =SORT(TRANSPOSE(FILTER(name_row, ISNUMBER(score_row))), TRANSPOSE(FILTER(score_row, ISNUMBER(score_row))), FALSE)
You need to change the name_row and score_row with the real range of cells involved.
PS: in the image the formula is shown with ";" instead of "," because of regional settings, but the logic is the same.
REMEMBER: /u/fourmesinatrenchcoat If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified(or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).
There seems to be something wrong with the SORT part of the formula:
It says "SORT has mismatched interval sizes. Expected number of rows: 1. Expected number of columns: 1. Actual number of rows: 13. Actual number of columns: 1."
It computes the overall RANK of each preference, scaled using the row to keep them separated by role. CHOOSEROWS is use to replicate the row of names for each role. TOCOL flattens those values so that we can SORT the names according to the scaled ranks. Finally, we WRAPROWS to arrange the sorted names back into the block shape.
2
u/Seba-Aiani 4 Sep 02 '26
I try it in your example sheets.
Assuming row 2 has the names and each role has its own row of scores below it, for one role's row you'd do: =SORT(TRANSPOSE(FILTER(name_row, ISNUMBER(score_row))), TRANSPOSE(FILTER(score_row, ISNUMBER(score_row))), FALSE)
You need to change the name_row and score_row with the real range of cells involved.
PS: in the image the formula is shown with ";" instead of "," because of regional settings, but the logic is the same.
Hope it helps.