Forum Discussion
Looking for help with a single Dynamic Formula Ranking Points by player and dropping lowest score
In cell E2 apply this formula:
=LET( d, A2:.C15, tk, TAKE, br, BYROW, ts, TEXTSPLIT, tj, TEXTJOIN, SORT(HSTACK(tk(d,, 1), br(--DROP(ts(tj(";",, br(--ts(tj(";",, CHOOSECOLS(d, 2)), ",", ";"), LAMBDA(a, ARRAYTOTEXT(CHOOSEROWS(SORT(tk(d,, -1),, -1), a))))), ", ", ";"),, -1), SUM)), 2, -1) )Hope this helps.
IlirU
My end goal is just getting a Standings Table to publish to my group the standings of our league.
My True Data looks like this (before I converted to a "challenge" type format. I'm sure there is an even better/shorter dynamic formula to get to desired results here:
| Tourney | Place | Name | Points | 1st | 2nd | 3rd | 4th | 5th | 6th | 7th | 8th | 9th |
| 1 | 1 | Andrew | 165 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 1 | 2 | Randy | 105 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 1 | 3 | Chris | 75 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
| 1 | 4 | Bill | 50 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
| 1 | 5 | Jeff | 35 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 |
| 1 | 6 | Reed | 25 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 |
| 1 | 7 | Doc | 20 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 |
| 1 | 8 | Steve | 15 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 |
| 1 | 9 | Paul | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
| 2 | 1 | Steve | 165 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2 | 2 | Paul | 105 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2 | 3 | Doc | 75 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2 | 4 | Reed | 50 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
| 2 | 5 | Randy | 35 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 |
| 2 | 6 | Andrew | 25 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 |
| 2 | 7 | Chris | 20 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 |
| 2 | 8 | Jeff | 15 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 |
| 2 | 9 | Bill | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
| 3 | 1 | Andrew | 165 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 3 | 2 | Chris | 105 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 3 | 3 | Paul | 75 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
| 3 | 4 | Randy | 50 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
| 3 | 5 | Doc | 35 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 |
| 3 | 6 | Steve | 25 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 |
| 3 | 7 | Bill | 20 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 |
| 3 | 8 | Jeff | 15 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 |
| 3 | 9 | Reed | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
| 4 | 1 | Chris | 165 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 4 | 2 | Jeff | 105 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 4 | 3 | Steve | 75 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
| 4 | 4 | Paul | 50 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
| 4 | 5 | Doc | 35 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 |
| 4 | 6 | Bill | 25 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 |
| 4 | 7 | Reed | 20 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 |
| 4 | 8 | Andrew | 15 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 |
| 4 | 9 | Randy | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
| 5 | 1 | Chris | 165 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 5 | 2 | Andrew | 105 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 5 | 3 | Paul | 75 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
| 5 | 4 | Reed | 50 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
| 5 | 5 | Steve | 35 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 |
| 5 | 6 | Bill | 25 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 |
| 5 | 7 | Jeff | 20 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 |
| 5 | 8 | Randy | 15 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 |
| 5 | 9 | Doc | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
| 6 | 1 | Bill | 165 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 6 | 2 | Paul | 105 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 6 | 3 | Jeff | 75 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
| 6 | 4 | Andrew | 50 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
| 6 | 5 | Randy | 35 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 |
| 6 | 6 | Doc | 25 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 |
| 6 | 7 | Reed | 20 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 |
| 6 | 8 | Chris | 15 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 |
| 6 | 9 | Steve | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
In cell O1 apply below formula:
=LET(
place, B2:B55,
name, C2:C55,
points, D2:D55,
dr, DROP(PIVOTBY(name, place, points, SUM,, 0,, 0), 1),
dp, DROP(dr,, 1),
hs, HSTACK(dr, BYROW(dp, SUM) - BYROW(dp, MIN)),
clm, COLUMNS(hs),
VSTACK(HSTACK("Name", SEQUENCE(, clm - 2), "Total"), SORT(hs, clm, -1))
)Hope this helps.
Note: Thank you for marking my previous reply as a Valid Answer.
IlirU
What would be really cool is if I can take that list above and create a formula that creates the "challenge" list with the sorted finishes all together in the one cell.
Name Finishes Andrew 1,1,2,4,6,8 Randy 2,4,5,5,8,9 Chris 1,1,2,3,7,8 Bill 1,4,6,6,7,9 Jeff 2,3,5,7,8,8 Reed 4,4,6,7,7,9 Doc 3,5,5,6,7,9 Steve 1,3,5,6,8,9 Paul 2,2,3,3,4,9 I can do it manually for one person :
=TEXTJOIN(", ",,SORT(FILTER(Data[Place],Data[Name]="Andrew")))
But I can't seem to get it to fill for all players dynamically. I might even then try to combine your first formula to get a 3rd column with total points; but that is not necessary.
Thanks again for all of your help. This has been a great education.
=REDUCE({"Name"."Finishes"."TotalPoints"},UNIQUE(Data[Name]), LAMBDA(u,v, VSTACK(u, HSTACK( v, TEXTJOIN(",",,SORT(FILTER(Data[Place],Data[Name]=v))), SUM(FILTER(Data[Points],Data[Name]=v)) ) ) ) )A formula solution could be with LAMBDA and REDUCE. However I'd prefer a Power Query solution as shown in the attached file.
A formula approach that works is to use
=GROUPBY( name, HSTACK(points, place), VSTACK( HSTACK(SUM, LAMBDA(x, TEXTJOIN(",", , SORT(x)))), {"Total points", "Finishes"} ), , 0,-2 )
