# Find and sort the highest X values in each row

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.

• You haven't explained how you want to handle ties. Oct 15, 2022 at 8:52
• 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 Oct 15, 2022 at 14:12

### Col headings for Nth largest values in row

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

the Basic Formula would be:

``````//  position = 1 (largest)

=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

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

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

``````=lambda(
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.