Find and sort the highest X values in each row

Question

Example file: https://docs.google.com/spreadsheets/d/1iESk7ccWBKBLE6Q00P9AxH3GbOYPO9PysySGbbQljjI/edit?usp=sharing

I have a table showing how many times each player used each item. For each player, I want to identify the X items they used most frequently (3 in the example). The results need to be the names of the items (column headers), not the number of uses.

The solution I've used so far (using BYROW function) is imperfect because it doesn't order them from highest to lowest, and returns all the ties. The first part in particular is where I'm stuck, but how I've dealt with the second part is very rough too.

Tied results should appear in the order of the headings, but are cut off at X. E.g. for top 3, results in order are Item 10 > Item 1 = Item 2 = Item 3 = Item 4, should just display Item 10, Item 1, Item 2.

=byrow(B2:Q10,lambda(row, if(counta(row)=0,,join(",",filter($B$1:$Q$1,row>=LARGE(row,3))))))

Any help would be greatly appreciated.

Answer 1

Col headings for N^th largest values in row

I approached this by writing a formula to return the value of the heading for a single N^th largest value.

Using the data in my example Google spreadsheet,
the Basic Formula would be:

//  position = 1 (largest)

=INDEX(headings,,MATCH(LARGE(data,position),data,0))
=INDEX(B2:R2,,MATCH(LARGE(B3:R3,1),B3:R3,0)) 
=Item 15

See cell "T3" in example spreadsheet.

To Automatically calculate for all rows:

 // Named Ranges
 
    data     = ranking!C3:R11
    headings = ranking!C2:R2


=BYROW(data,LAMBDA(r,INDEX(headings,,MATCH(LARGE(r,1),r,0))))
=BYROW(B3:R11,LAMBDA(r,INDEX(B2:R2,,MATCH(LARGE(r,1),r,0))))
=   Item 15
    Item 1
    Item 12
    Item 14
    Item 9
    Item 9
    Item 9
    Item 16
    Item 12

See column "V" ("W" and "X") in example spreadsheet.

Not sure why you are concatenating the results as I don't understand the use case but if you like "Z3" in example spreadsheet shows that as well.

 Item 15, Item 5, Item 1
 Item 1, Item 1, Item 7
 Item 12, Item 9, Item 16
 Item 14, Item 16, Item 9
 Item 9, Item 9, Item 5
 Item 9, Item 11, Item 11
 Item 9, Item 9, Item 16
 Item 16, Item 15, Item 7
 Item 12, Item 5, Item 5

Answer 2

This can also be done using sortn() in a byrow() lambda with an arrayformula(split()) wrapper, like this:

=lambda( 
  headers, sortKeysTable, numLargestToShow, 
  arrayformula( 
    split( 
      byrow( 
        sortKeysTable, 
        lambda( 
          sortKeys, 
          join( "→", 
            transpose( 
              sortn( transpose(headers), numLargestToShow, 0, transpose(sortKeys), false ) 
            ) 
          ) 
        ) 
      ), 
      "→"  
    ) 
  ) 
)( 
  B1:Q1, B2:Q10, 3 
)

See the anonymously editable sample spreadsheet Blind Spots kindly provided.