Displaying the row header for highest X values in each row (for ranking list)

0
votes

I'm having trouble trying to rank and display the popularity of certain items in a table in Excel. An image showing a simplified example of what I'm trying to create is at this link (it won't let me post images, because I'm still new here):

enter image description here

For each row (or shape, here), I want to display in columns H,I and J, the column Header (in this case, the colour) for the most popular three types.

I've tried variations of the following formula for Column H (most popular):

=INDEX($A$2:$F$2,1,MATCH(LARGE($A3:$F3,1),$A3:$F3,0))

...and then, change the "1" in the LARGE() function to "2" and "3" for Columns I and J.

That works for finding the largest number and returning the column Header for H, but it can't handle when there is more than one instance of a particular number in the row.

Example given: for Triangles (Row 5), it comes up with "Blue" in Columns H, I and J, instead of looking for the next equal/lower number. And for Circles, it gives me "Blue", "Blue", "Green" - it knows that Green (2) is lower than Blue (3), but can't tell that Yellow (3) is also higher than Green, because once it finds the first instance of the highest number (in this case, Blue), it stops searching through.

I don't mind that it doesn't recognise that Green is actually 'first equal' rather than 2nd. I'm happy for it to be prioritised from left to right, but I need it to show the top three (and more, once I expand) without duplicating the results.

I'd like to avoid any VBA if I can, and feel like there must be a way to do this easily.

1
excelexcel-formula

1 Answers

1
votes

A possible workaround

I assume the values are integers

The idea is to subtract a very small value multiplied by the column to each value so that there are no equal values

Create a second Sheet (I assume the sheetnames are Sheet1 and Sheet2)

Put the formulas in Sheet2:

A3 | = Sheet1!B3-0.00001*COLUMN()| = Sheet1!C3-0.00001*COLUMN()| = Sheet1!D3-0.00001*COLUMN()| = Sheet1!E3-0.00001*COLUMN()| = Sheet1!F3-0.00001*COLUMN()|
A4 | = Sheet1!B4-0.00001*COLUMN()|...
A5 | ...
....

And change the formula to "=INDEX($ B $2:$F$2,1,MATCH(LARGE(Sheet2!$ B 3:$F3,1),Sheet2!$ B 3:$F3,0))"