Google Sheets - how can I return votes by team, sorted by the most popular votes?

Question

I have a sheet with participant votes, and each participant is in a team. In a second sheet, I have the following formula to create a subset of participant votes per team (followed by voter name) in each row.

=if($A:$A="","",arrayformula(textjoin(" ",true,sort(if(Score=10,"",if(Team=A2,REGEXREPLACE(Improve&" - "&Detail&" ("&Name&")","/n"," "),"")),1,true))))

How can I get the results sorted in decreasing order of votes (show the most popular votes first, then the 2nd most popular, etc.?

In case there are two or more topics with the same number of votes, it would make sense to use voter names as secondary sort criteria.

Here's a link to a sheet with sample data.

I've tried using the SORT() function (column C in the second sheet), sorting the results alphabetically, so at least the votes are effectively grouped, but I need to display the most popular issues first, as shown in column D.

The query formula in E1 was provided as answer to this similar question and I've tried manipulating the formula, without success.

Answer 1

try:

=INDEX(SUBSTITUTE(SUBSTITUTE(TRIM(SPLIT(FLATTEN(QUERY(QUERY({SORT(FILTER(
 {data!B2:B&"×", COUNTIF(data!B2:B&data!D2:D, data!B2:B&data!D2:D), data!D2:D&"♥("&data!A2:A&")"}, data!C2:C<10), 1, 1, 2, 0, 3, 1), 
 SEQUENCE(ROWS(data!C2:C)-COUNTIFS(data!C2:C, 10))}, 
 "select max(Col3) where not Col1 starts with '×' group by Col4 pivot Col1"),,9^9)), "×")), " ", CHAR(10)), "♥", " "),,2)

or with previous:

={"Team", "Scores", "What needs improvement"; 
 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)))}), 
 INDEX(SUBSTITUTE(SUBSTITUTE(TRIM(SPLIT(FLATTEN(QUERY(QUERY({SORT(FILTER(
 {data!B2:B&"×", COUNTIF(data!B2:B&data!D2:D, data!B2:B&data!D2:D), data!D2:D&"♥("&data!A2:A&")"}, data!C2:C<10), 1, 1, 2, 0, 3, 1), 
 SEQUENCE(ROWS(data!C2:C)-COUNTIFS(data!C2:C, 10))}, 
 "select max(Col3) where not Col1 starts with '×' group by Col4 pivot Col1"),,9^9)), "×")), " ", CHAR(10)), "♥", " "),,2)}

demo spreadsheet