CODEWITHSUNDEEP
arrayssortinggoogle-sheetsvlookuprank
Bohdan Mukha
Bohdan Mukha

Reputation: 23

How to rank teams in google sheets based on two columns?

Image of the spreadsheet

I have a spreadsheet with teams I want to rank. I have two columns - the first one is the main criteria teams should be ranked by, and the second one should be a tie-breaker when the first one is equal. If values in both columns are equal, teams should share the places. So, basically what I try to achieve is at least to rank teams based on two columns like in the "Kinda ok outcome" column. I've tried to do this using the built-in RANK function, but it only works with one column. In the best-case scenario, if it's even possible, I would like to format ranked places in a manner provided in the "Perfect outcome" column.

Upvotes: 2

Views: 1579

Answers (2)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(IF(A2:A="";;RANK(1*(B2:B&C2:C); 1*(B2:B&C2:C); 0)))

enter image description here

=ARRAYFORMULA(IF(A2:A="";;REGEXREPLACE(""&
 RANK(1*(B2:B&C2:C); 1*(B2:B&C2:C); 0)&"-"&VLOOKUP(
 RANK(1*(B2:B&C2:C); 1*(B2:B&C2:C); 0); SORT({
 RANK(1*(B2:B&C2:C); 1*(B2:B&C2:C); 0)\ IFERROR(
 RANK(1*(B2:B&C2:C); 1*(B2:B&C2:C); 0)+IFERROR(1/(1/(
 COUNTIFS(B2:B&C2:C; B2:B&C2:C; ROW(B2:B); "<="&ROW(B2:B))-1)); 
 "×"))}; 2; 0); 2; 0); "-$"; )))

enter image description here

for negative numbers:

=ARRAYFORMULA(IF(B3:B8="",,RANK(1*((C3:C8&"0000")+D3:D8), 1*((C3:C8&"0000")+D3:D8), 0)))

enter image description here

Upvotes: 2

Digital Farmer
Digital Farmer

Reputation: 2137

For this work, I recommend using a simpler mode, which would be the function SORT:

=SORT(A2:E15,2,FALSE,3,FALSE)

Your region seems to use ; instead of , so in your specific case it would look like this:

=SORT(A2:E15;2;FALSE;3;FALSE)

The result is this:

enter image description here

Upvotes: 0

Related Questions