Forum Discussion
Solved
Looking for help with a single Dynamic Formula Ranking Points by player and dropping lowest score
Name Finishes Andrew 1,1,2,3,7,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 Points (1st, 2n...
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
An alternative could be Power Query which works in Excel versions starting from Excel 2010. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
Very cool. I definitely need to step up my Power Query game. I went ahead and deleted the middle columns. Thanks for the solution.
