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
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 )This is a 'prettied up' version of the above that defines a Lambda function for the calculation.
Worksheet formula = SUMMARISEλ(name, place) where SUMMARISEλ = LAMBDA(name, place, LET( JOINλ, LAMBDA(x, TEXTJOIN(",", , SORT(x))), header, {"Total points", "Finishes"}, points, INDEX(pointList, place), GROUPBY( name, HSTACK(points, place), VSTACK(HSTACK(SUM, JOINλ), header), 0, 0,-2 ) ) );
Thank you very much. I have so much to review.
