I have a table with 21 teams. Every team has their scores (from 0 to 10 values).
I need to take the 10 highest scores of all teams and put them in front of the scores owners.
Could anybody help to write the formula?
Table: https://docs.google.com/spreadsheets/d/1w4vRedVPt4ArFoWlHeQB_xwk9dHrBYdK1iaqLr70rkY/edit?usp=sharing
I tried to write the formula, but i couln't manage it. too complex for me...
One way you can process a matrix of data in Google Sheet is by using Byrow and Bycol. I created a formula for you to use.
Try this:
=LET(range, C3:L25, lastnum, INDEX(SORT(TOCOL(range),1,FALSE),10,1),BYROW(range, LAMBDA(row, BYCOL(row, LAMBDA(column, IF(column >= lastnum, column, " "))))))
Updated Formula in Case you are posting from different Sheet
=LET(range, 'Team values'!C3:L25, lastnum, INDEX(SORT(TOCOL(range),1,FALSE),10,1),BYROW(range, LAMBDA(row, BYCOL(row, LAMBDA(column, IF(column >= lastnum, column, " "))))))
Sample Output:
Reference: