Search code examples
excelexcel-formula

How to return ordered, unique data in Excel?

Last year, I asked a question on SuperUser about a similar issue. The answer was great and the formula has been working as I expected. Recently, I've been asked to add some additional columns to the list.

My input data now looks like this:

Name Sex Total Event Team
Taylor f 268 Event1 Team A
Taylor f 252 Event 2 Team A
Halle f 251 Event 1 Team C
Christian m 410 Event 2 Team D
abc m 216 Event 3 Team E

What I'm trying to do is return a list of the Top N unique men or women by total, with the name, total, event, and team.

The function that was provided last year looks like this

=LET(
    data, $A$1:$C$15,
    criteria, "f",
    topN, 3,
    filteredData, FILTER(data, INDEX(data, , 2) = criteria),
    uniqueNames, UNIQUE(INDEX(filteredData, , 1)),
    totals, SUMIFS(INDEX(data, , 3), INDEX(data, , 1), uniqueNames, INDEX(data, , 2), criteria),
    maxTotals,MAXIFS(INDEX(data,,3),INDEX(data,,1),uniqueNames,INDEX(data,,2),criteria),
    sortedNames, INDEX(uniqueNames, MATCH(LARGE(totals, SEQUENCE(topN)), totals, 0)),
    sortedTotals, INDEX(maxTotals, MATCH(LARGE(totals, SEQUENCE(topN)), totals, 0)),
    HSTACK(SEQUENCE(topN), sortedNames, sortedTotals)
)

I now need to also include the event, and the team associated with the person, and their total. I've tried to update the function but have been unable to get it to work. I can't figure out how to reference the name and total being returned within the LET() statement. I figure I need to use either INDEX/MATCH or XLOOKUP with multiple criteria, but I can't figure out how to structure the formula.

Thanks

Solution

  • Get Top Unique

    =LET(list,A1:E21,top,H1,gender,H2,name_col,1,total_col,3,gender_col,2,
        return_cols,{1;3;4;5},include_headers,1,
    d,DROP(list,1),
    f,FILTER(d,CHOOSECOLS(d,gender_col)=gender),
    s,SORT(f,total_col,-1),
    n,CHOOSECOLS(s,name_col),
    u,CHOOSEROWS(s,XMATCH(UNIQUE(n),n)),
    t,TAKE(u,top),
    h,IF(include_headers,VSTACK(TAKE(list,1),t),t),
    r,CHOOSECOLS(h,return_cols),
    r)
    • Replace the last r with any other variable to see what it holds.

    enter image description here

    EDIT

    • To be able to include a Rank column, you could use:
    =LET(list,A1:E21,top,H1,gender,H2,name_col,1,total_col,3,gender_col,2,
        return_cols,{1;3;4;5},include_headers,1,rank_title,"Rank",include_rank,1,
    d,DROP(list,1),
    f,FILTER(d,CHOOSECOLS(d,gender_col)=gender),
    s,SORT(f,total_col,-1),
    n,CHOOSECOLS(s,name_col),
    u,CHOOSEROWS(s,XMATCH(UNIQUE(n),n)),
    t,TAKE(u,top),
    h,IF(include_headers,VSTACK(TAKE(list,1),t),t),
    rh,CHOOSECOLS(h,return_cols),
    r,IF(include_rank,HSTACK(IF(include_headers,
        VSTACK(rank_title,SEQUENCE(top)),SEQUENCE(top)),rh),rh),
    r)