Returning Row Heading for Top 'X' values in a range

Needing some help if possible please.

I have a sheet of data of 50R x 60C containing data represented as percentages. On a separate sheet I want to generate a series of columns which shows the Top 'X' values for each column in the data range but instead of returning the value found for each rank, I want to return the value of the row heading. If possible I'd like to be able to change how many values I wish to return.

An example of the data

[4][ROW1][22%][12%][78%]...[1%]
[5][ROW2][8%][51%][9%]...[90%]
[6][ROW3][45%][83%][56%]...[27%]
.
.
.
[54][ROW50][1%][7%][14%]...[88%]

I guess basically what I am trying to do is a reverse intersection. I tried using LARGE and OFFSET but wasn't really getting anywhere. I am using Excel 2007 too.

I tried looking at some detailed examples of using RANK from http://www.cpearson.com/excel/rank.aspx but I couldn't seem to get some of the suggested formulas working for me.

Any help would be appreciated.

Originally Posted by Powermonger

Needing some help if possible please.

I have a sheet of data of 50R x 60C containing data represented as percentages. On a separate sheet I want to generate a series of columns which shows the Top 'X' values for each column in the data range but instead of returning the value found for each rank, I want to return the value of the row heading. If possible I'd like to be able to change how many values I wish to return.

An example of the data

[4][ROW1][22%][12%][78%]...[1%]
[5][ROW2][8%][51%][9%]...[90%]
[6][ROW3][45%][83%][56%]...[27%]
.
.
.
[54][ROW50][1%][7%][14%]...[88%]

I guess basically what I am trying to do is a reverse intersection. I tried using LARGE and OFFSET but wasn't really getting anywhere. I am using Excel 2007 too.

I tried looking at some detailed examples of using RANK from http://www.cpearson.com/excel/rank.aspx but I couldn't seem to get some of the suggested formulas working for me.

Any help would be appreciated.

A couple of questions.

Does the data above exist exactly as you've displayed it? i.e. does that table start in cell A1, and therefore does A1 = [4] , B1 = [Row1], C1 = [22%] - all with the square brackets included?

What does the 'row heading' mean? Is that column A, and hence the row heading for the first row is [4], or is it just 4 ?

And taking the 4 rows shown above, if you want the row headings of the top 2 items from say column 3, (which are 45% and 22%) is the result you want therefore [6] and [4] - in that order? Or perhaps 6 and 4?

Regards

Originally Posted by NBVC

So if your row headers are in A1:A4 and your corresponding data is in B1:E3, then

=INDEX($A$1:$A$3,MATCH(LARGE(B1:B3,1),B1:B7,0))

will return row header corresponding to largest value in the first column on data (B1:B3)... you can copy the formula to the right to get next column's corresponding row header and so on.

Is that what you need?

Thanks that formula worked, it is returning the same row however when it comes across situations where two or more values are equal. I'll try and get around that if I can.

Originally Posted by Richard Buttrey

A couple of questions.

Does the data above exist exactly as you've displayed it? i.e. does that table start in cell A1, and therefore does A1 = [4] , B1 = [Row1], C1 = [22%] - all with the square brackets included?

What does the 'row heading' mean? Is that column A, and hence the row heading for the first row is [4], or is it just 4 ?

And taking the 4 rows shown above, if you want the row headings of the top 2 items from say column 3, (which are 45% and 22%) is the result you want therefore [6] and [4] - in that order? Or perhaps 6 and 4?

Regards

The [4] represents just the row number, [ROW1] is the cell with the row heading I have designated and wish to return, the [22%] and other values are the data I am evaluating to find the top 10 or 15 of and then pair up their corresponding row heading with.

Still not getting anywhere unfortunately.

Here is some sample results using NBVC's suggested formula:

Ref 97%
Han 93%
Con 86%
Agi 79%
Agi 79%
Agi 79%
Bra 76%
Bra 76%
Bra 76%
Bra 76%

These are two columns of data, the first column uses the formula provided and the 2nd column is just the LARGE function used against the same data but without returning the row reference label.

As you can see, the original column data these formulas are processing have a few entries which are equal in value. The 4th highest value is returned correctly, but with the 5th and 6th, instead of moving onto the next value which is equal to the 4th, it just returns 4th again. How can I make the formula ignore a previously returned entry and move down the list to the next entry equal in value?