Return Column Headers Based On LARGE function

[mgirvin]

Return Column Headers Based On LARGE function

Dear Most Amazing Excelers In The World,

In the cell range A1:F3, I have this data:

Day EP US IL DK Mex
Day 100 4 15 13 10 9
Day 101 3 5 11 5 9

And I would like to create a formula that will return the column headers based on the largeness of the values in each row of numbers (large 1, large 2, large 3, large 4, large 5). The values I would like the formula to return are here:

Day Large 1 Large 2 Large 3 Large 4 Large 5
Day 100 US IL DK Mex EP
Day 101 IL Mex US DK EP

This is because for the:
First row, US = Large 1 = 15, IL = Large 2 = 13, DK = Large 3 = 10, Mex = Large 4 = 9, and EP = Large 5 = 4
Second row IL = Large 1 = 11, Mex = Large 2 = 9, US = 1st Large 3 in row = 5, DK = 2nd Large 3 in row = 5, and EP = Large 5 = 3

I have created this formula:

{=INDEX($B$1:$F$1,,SMALL(IF($B2:$F2=LARGE($B2:$F2,COLUMNS($I11:I11)),COLUMN($B$1:$F$1)-COLUMN($B$1)+1),COUNTIF($B2:$F2,LARGE($B2:$F2,COLUMNS($I11:I11)))))}

But I get this data returned:

Day Large 1 Large 2 Large 3 Large 4 Large 5
Day 100 US IL DK Mex EP
Day 101 IL Mex DK DK EP

I cannot seem to get my formula to deal with duplicates. In the second row I get 2 DKs instead of US and then DK.

Can any of you Formula Excel Masters help me?

[Domenic]

Try...

I2, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=INDEX($B$1:$F$1,MATCH(SMALL(RANK($B2:$F2,$B2:$F2)+COLUMN($B2:$F2)/1000,COLUMNS($I2:I2)),RANK($B2:$F2,$B2:$F2)+COLUMN($B2:$F2)/1000,0))

Hope this helps!

[mgirvin]

Dear Domenic,

Wow! Once again, your Excel Magic is amazing! It helps a great deal, not only that but I get to learn a few new Excel concepts such as:

Using the RANK to get an array of ranked vales
and
Adding just a little bit extra to break the ties

I have this question though:

Why are you putting a row number into the index function when the values are sitting in columns?

I tried your formula:

=INDEX($B$1:$F$1,MATCH(SMALL(RANK($B2:$F2,$B2:$F2)+COLUMN($B2:$F2)/1000,COLUMNS($I2:I2)),RANK($B2:$F2,$B2:$F2)+COLUMN($B2:$F2)/1000,0))

where the "MATCH(SMALL(RANK($B2:$F2,$B2:$F2)+COLUMN($B2:$F2)/1000,COLUMNS($I2:I2)),RANK($B2:$F2,$B2:$F2)+COLUMN($B2:$F2)/1000,0)" puts a row number into the INDEX.

Then I tried it this way:

=INDEX($B$1:$F$1,,MATCH(SMALL(RANK($B4:$F4,$B4:$F4)+COLUMN($B4:$F4)/1000,COLUMNS($I4:K4)),RANK($B4:$F4,$B4:$F4)+COLUMN($B4:$F4)/1000,0))

where the "MATCH(SMALL(RANK($B2:$F2,$B2:$F2)+COLUMN($B2:$F2)/1000,COLUMNS($I2:I2)),RANK($B2:$F2,$B2:$F2)+COLUMN($B2:$F2)/1000,0)" puts a column number into the INDEX.

It worked both ways. Does this mean that when the array in the INDEX is in columns, you can give the INDEX a row number or a column number and it will work?

Thanks so much Domenic!!!!!

[Domenic]

Originally Posted by mgirvin

...It worked both ways. Does this mean that when the array in the INDEX is in columns, you can give the INDEX a row number or a column number and it will work?

Yes, that's right. If the indexed array, in this case B1:F1, contains only one row or column, the corresponding row_num or column_num argument is optional.

Thanks so much Domenic!!!!!

You're very welcome!

[mgirvin]

Dear Domenic,

Now I can add that to my list of things learned from your elegant formula.

Thanks for the formula and the new Excel concepts!