Creating a ranking in Excel by matching values in non-contigous cells

I have an excel file that looks like this:

   A  B  C  D  E        F  G  H  I  J      K  L  M  N  O  
1  Alice                Bob                Charles
2  10 35 54 9 21        71 15 43 75 98     13 35 66 80 20

where each group of 5 columns reports some data relative to a given person.

I want to build five rankings based on the values in the 5 columns of each group. For example, the ranking of the columns "A, F, K" should be "Bob, Charles, Alice" (because the value in A of Bob is 71, the value in A of Charles is 13, and the value in A of Alice is 10). Similarly, the ranking of "B, G, L" would either be "Alice, Charles, Bob" or "Charles, Alice, Bob" (because there is a tie: 35, 35, 15).

I suppose I should use a mix of INDEX/MATCH, (V)LOOKUP and LARGE but don't really know where to start. The furthest I got is something along the lines of

LARGE((A2, F2, K2), 1)
LARGE((A2, F2, K2), 2)
LARGE((A2, F2, K2), 3)

That (should) output the first, second and third largest value for the range "A2, F2, K2", but I don't know how to get to the name of the person related to that value from there. I have some troubles generalizing the examples I found with INDEX/MATCH and lookup functions to this atypical data structure (in groups of five columns).

EDIT: The names (Alice, Bob, Charles) are on merged cells.

st1led

Posted 2017-02-08T14:04:50.907

Reputation: 121

You have certainly mentioned 'non-contiguous cells', however just to confirm, the names of people e.g. Alice, Bob, are they in a single cell with blanks in between or merged cells? – patkim – 2017-02-08T14:08:38.883

The names are on merged cells. – st1led – 2017-02-08T14:20:52.670

Are all 15 values always distinct? Can there be repeating values therein? – patkim – 2017-02-08T14:21:11.087

Yes, there can be repeated values, as explained in the second example for the ranking of B, G, L (there is a tie on the value 35, in that case the order in the ranking doesn't matter). – st1led – 2017-02-08T14:23:28.953

Is this the only data you have in your Excel file? e.g. Do you have repeating data in Row 3 & 4 on same lines as 1 & 2? – patkim – 2017-02-08T15:03:11.797

I do not have repeating data in Rows 3 & 4, but I have around 10 different "blocks" of 10 people say, each of whom has around 10 different values (in 10 columns). – st1led – 2017-02-10T11:52:27.850

Answers

You can write the Data differently in order to use Rank:

In E2 =RANK($B2,$B2:$D2,0)
In F2 =RANK($C2,$B2:$D2,0)
In G2 =RANK($D2,$B2:$D2,0)

And you can drag each formula in the same column

yass

Posted 2017-02-08T14:04:50.907

Reputation: 2 409

Thank you for your proposed solution, but what I'm looking for is a Ranking where the content of the cells is a ranking with names, rather than with numbers.

This means that I need rankings that looks like

  Alice         Bob            Charles
  Bob          Charles       Bob
  Charles    Alice           Alice

Rather than a table with a number stating the people position in each rank – st1led – 2017-02-10T11:44:07.430

I am suggesting a solution however it is only suitable if this is the only data in you sheet. If you have repeating rows below on similar lines on rows 1 & 2 then this shall become an inefficient solution and you may need to opt for VBA probably.

See the screenshot below.

The matrix is aligned in Range G7:J12.

Formula in H8

=IF(CHOOSE(1,$A$2,$F$2,$K$2)=LARGE(($A$2,$F$2,$K$2),1),$A$1,IF(CHOOSE(2,$A$2,$F$2,$K$2)=LARGE(($A$2,$F$2,$K$2),1),$F$1,$K$1))

=IF(CHOOSE(1,$B$2,$G$2,$L$2)=LARGE(($B$2,$G$2,$L$2),1),$A$1,IF(CHOOSE(2,$B$2,$G$2,$L$2)=LARGE(($B$2,$G$2,$L$2),1),$F$1,$K$1))

H10

=IF(CHOOSE(1,$C$2,$H$2,$M$2)=LARGE(($C$2,$H$2,$M$2),1),$A$1,IF(CHOOSE(2,$C$2,$H$2,$M$2)=LARGE(($C$2,$H$2,$M$2),1),$F$1,$K$1))

H11

=IF(CHOOSE(1,$D$2,$I$2,$N$2)=LARGE(($D$2,$I$2,$N$2),1),$A$1,IF(CHOOSE(2,$D$2,$I$2,$N$2)=LARGE(($D$2,$I$2,$N$2),1),$F$1,$K$1))

H12

=IF(CHOOSE(1,$E$2,$J$2,$O$2)=LARGE(($E$2,$J$2,$O$2),1),$A$1,IF(CHOOSE(2,$E$2,$J$2,$O$2)=LARGE(($E$2,$J$2,$O$2),1),$F$1,$K$1))

Now drag the respective formulas across to the right and make a slight modification in the LARGE function. For second column i.e. Col I put the second parameter as 2 for both LARGE therein and for the third column i.e. Col J, put it 3.

Honestly discard this solution if you have repeating rows below and you need to do it for every set or rows therein.

patkim

Posted 2017-02-08T14:04:50.907

Reputation: 3 699

This is exactly the type of ranking I need. I'll try to adapt your example to my data, but as you suspected this is more complicated. I have around 10 people in total, each of whom has around 10 different types of data (say something like 10 rankings of length 10). I might be able to do it, but I think I need 9 of these "IF / CHOOSE" constructs, seems complicated. – st1led – 2017-02-10T11:49:55.887

1Excel has limitation of 7 Nested IF's. Even maintaining 7 nested IF's is bulky too. There might be a smarter solution but this is what I could think of for the moment! If you are familiar with VBA Coding you can opt for that too as it will be less complex and solvable at a click of a button. – patkim – 2017-02-10T12:34:54.283