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
I don't understand exactly what you are trying to accomplish, so I think it would be best if you posted a screenshot so that I can understand where your data is located and manually set the results you want to get from that data. So explain your problem better so that I or another contributor to this forum can try to help you.
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.
