Forum Discussion

Copper Contributor

Mar 06, 2026

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...

Formulas and Functions

IlirU

Mar 09, 2026

willwonka,

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

Iron Contributor

Mar 10, 2026

willwonka,

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

willwonka

Copper Contributor

Mar 12, 2026

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.

OliverScheurich

Gold Contributor

Mar 12, 2026

=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.

Looking for help.xlsx21 KB

PeterBartholomew1

Silver Contributor

Mar 12, 2026

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
)

PeterBartholomew1

Silver Contributor

Mar 13, 2026

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
        )
    )
);

Looking for help.xlsx29 KB

willwonka
Copper Contributor
Mar 12, 2026
Thank you very much. I have so much to review.