Hi Everyone:
I need a fix to this rank formula returning repeated values when the two last columns match.
Is it possible to add a tiebreaker, related for example to the first column?
Otherwise, the fix can be applied to the index match formula, whatever is easier or you find to be more effective. I'm adding the original table, how the ranked table currently looks like and how it is supposed to look when fixed.
Thanks
In AF2, I am using:
=RANK.EQ($J2;$J$2:$J$17)+COUNTIFS($J$2:$J$17;$J2;$I$2:$I$17;">"&$I2)
In L2 I am using:
=IFERROR(INDEX($B$2:$J$17;MATCH(A2;$AF$2:$AF$17;0);1);"")
I need a fix to this rank formula returning repeated values when the two last columns match.
Is it possible to add a tiebreaker, related for example to the first column?
Otherwise, the fix can be applied to the index match formula, whatever is easier or you find to be more effective. I'm adding the original table, how the ranked table currently looks like and how it is supposed to look when fixed.
Thanks
In AF2, I am using:
=RANK.EQ($J2;$J$2:$J$17)+COUNTIFS($J$2:$J$17;$J2;$I$2:$I$17;">"&$I2)
In L2 I am using:
=IFERROR(INDEX($B$2:$J$17;MATCH(A2;$AF$2:$AF$17;0);1);"")
| Book1 | ||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | |||
| 1 | J | V | E | D | M | S | G | P | J | V | E | D | M | S | G | P | J | V | E | D | M | S | G | P | ||||||||||
| 2 | 1 | ACA | 1 | 0 | 1 | 0 | 1 | 1 | 0 | 1 | CAA | 1 | 1 | 0 | 0 | 4 | 0 | +4 | 3 | CAA | 1 | 1 | 0 | 0 | 4 | 0 | +4 | 3 | 5 | |||||
| 3 | 2 | BAI | 1 | 0 | 0 | 1 | 0 | 4 | -4 | 0 | PET | 1 | 1 | 0 | 0 | 3 | 1 | +2 | 3 | PET | 1 | 1 | 0 | 0 | 3 | 1 | +2 | 3 | 16 | |||||
| 4 | 3 | CAA | 1 | 1 | 0 | 0 | 4 | 0 | +4 | 3 | PRI | 1 | 1 | 0 | 0 | 1 | 0 | +1 | 3 | PRI | 1 | 1 | 0 | 0 | 1 | 0 | +1 | 3 | 1 | |||||
| 5 | 4 | CCU | 1 | 0 | 1 | 0 | 1 | 1 | 0 | 1 | PRO | 1 | 1 | 0 | 0 | 2 | 1 | +1 | 3 | 5 | ||||||||||||||
| 6 | 5 | DES | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | ACA | 1 | 0 | 1 | 0 | 1 | 1 | 0 | 1 | ACA | 1 | 0 | 1 | 0 | 1 | 1 | 0 | 1 | 5 | |||||
| 7 | 6 | FER | 1 | 0 | 1 | 0 | 1 | 1 | 0 | 1 | CCU | 1 | 0 | 1 | 0 | 1 | 1 | 0 | 1 | 5 | ||||||||||||||
| 8 | 7 | INT | 1 | 0 | 0 | 1 | 0 | 1 | -1 | 0 | DES | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | 13 | ||||||||||||||
| 9 | 8 | LIB | 1 | 0 | 0 | 1 | 1 | 2 | -1 | 0 | FER | 1 | 0 | 1 | 0 | 1 | 1 | 0 | 1 | 13 | ||||||||||||||
| 10 | 9 | MAQ | 1 | 0 | 0 | 1 | 1 | 3 | -2 | 0 | SAG | 1 | 0 | 1 | 0 | 1 | 1 | 0 | 1 | 15 | ||||||||||||||
| 11 | 10 | PET | 1 | 1 | 0 | 0 | 3 | 1 | +2 | 3 | SCC | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | 2 | ||||||||||||||
| 12 | 11 | PRI | 1 | 1 | 0 | 0 | 1 | 0 | +1 | 3 | SRC | 1 | 0 | 1 | 0 | 1 | 1 | 0 | 1 | 3 | ||||||||||||||
| 13 | 12 | PRO | 1 | 1 | 0 | 0 | 2 | 1 | +1 | 3 | WIL | 1 | 0 | 1 | 0 | 1 | 1 | 0 | 1 | 3 | ||||||||||||||
| 14 | 13 | SAG | 1 | 0 | 1 | 0 | 1 | 1 | 0 | 1 | INT | 1 | 0 | 0 | 1 | 0 | 1 | -1 | 0 | INT | 1 | 0 | 0 | 1 | 0 | 1 | -1 | 0 | 5 | |||||
| 15 | 14 | SCC | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | LIB | 1 | 0 | 0 | 1 | 1 | 2 | -1 | 0 | 5 | ||||||||||||||
| 16 | 15 | SRC | 1 | 0 | 1 | 0 | 1 | 1 | 0 | 1 | MAQ | 1 | 0 | 0 | 1 | 1 | 3 | -2 | 0 | MAQ | 1 | 0 | 0 | 1 | 1 | 3 | -2 | 0 | 5 | |||||
| 17 | 16 | WIL | 1 | 0 | 1 | 0 | 1 | 1 | 0 | 1 | BAI | 1 | 0 | 0 | 1 | 0 | 4 | -4 | 0 | BAI | 1 | 0 | 0 | 1 | 0 | 4 | -4 | 0 | 5 | |||||
Sheet1 | ||||||||||||||||||||||||||||||||||
???
