Google Sheets - how can I return participants scores by team, sorted by score?

Question

I have a sheet with participant scores, and each participant is in a team. In a second sheet, I have the following formula to create a subset of participant scores per team in each row.

Here's a link to a sheet with sample data showing my formula:

How can I get the results sorted in decreasing numerical order?

I've tried using the SORT() function (column C in the second sheet), but understandably, it interprets the results as text, so a score of 10 is ranked lowest.

Answer 1

try this superior formula after you delete everything in range A:B:

={"Team", "Scores"; INDEX({UNIQUE(SORT(FILTER(data!B2:B, data!B2:B<>""))), 
 FLATTEN(SUBSTITUTE(TRIM(QUERY(REGEXREPLACE(SORT(QUERY(
 QUERY({data!B2:B, TEXT(data!C2:C, "000000")&CHAR(13)&"("&data!A2:A&")"}, 
 "select min(Col2) where Col1 is not null group by Col2 pivot Col1"),
 "offset 1", 0), SEQUENCE(COUNTA(data!A2:A)), 0), 
 "^0{1,5}", ),,9^9)), " ", CHAR(10)))})}