Sorting and Returning Values using Formula

[jgray]

Hi.

Okay. Here is what i am trying to do. I have a sheet with text column headers and text row labels, but the data is numerical. I would like a formula that sorts the data Large to Small based on the data found in a specific column, and then returns not he values of the top 10 for that column, but the row lables for the top 10 values.

I have attached an excel sheet for example. In this example, the formula would search for the column labeled Math, sort the entire data set based on high-low math scores, and return the names of the students in the high-low order based on their scores for math.

Any thoughts?Column Sort Across Sheet by Value, return Row Label.xlsx

[jason.b75]

In A12 enter

=INDEX($A$2:$A$5,MATCH(LARGE($B$2:$B$5,ROWS(A$12:A12)),$B$2:$B$5,0))

and fill down as required.

[martindwilson]

use a helper/rank unique then index match

[Pete_UK]

The attached returns both the name and the scores based on the column chosen from the pull-down on C11 or G11. Both methods use INDIRECT, the blue area uses R1C1 notation (slightly shorter), the pink area uses normal A1 notation.

Hope this helps.

Pete

[martindwilson]

that's got to be the most convoluted method i've ever seen and it goes wrong if 2 people have same score
similar result different way attached (and i'm also thinking sumproduct but i can't see it as yet)

[Pete_UK]

that's got to be the most convoluted method i've ever seen

Sometimes you get a train of thought and you just have to see it through ... <bg>

I did think about building in a tie-break, but then I thought I'd wait to see what feedback if any we got from the OP.

Pete

[MarvinP]

Hi jgray and others,

I find a lot of these problems that have Cross Tab tables and the OP wants an answer. If they would arrange their data into a normal table (like the attached) they can do the problem without needing any formula. Using a Pivot Table!!! See the attached where the Filter is Subject and sorted by the values from high to low. The names then arrange correctly. No formulas, no indexes, no ranks. If the OP will get back to me, I have code to change Cross Tab tables to normal Pivot Tables.

[martindwilson]

@pete uk..i know what you mean..you go down a path and that's it, a 200 character formula then someone comes back with "use a pivot table!

[Pete_UK]

... then someone comes back with "use a pivot table!

Yeah, but Marvin's solution involves the OP doing a lot of manual manipulation of the data. I suspect that there are a lot more students and subjects in the OP's real data, so I'm not sure how willing they would be to put the data in that format first.

Pete

[jason.b75]

you go down a path and that's it, a 200 character formula

I didn't count, but it looks close

Please Login or Register  to view this content.

No helpers and it works with tie-breaks. Array confirmed in A12 of the OP's sample file.

[Marcol]

@ Martin
Re:

that's got to be the most convoluted method i've ever seen ...

You aint see nufink yet ...

@ jgray
Try this workbook, it uses Dynamic Named Ranges, and a Dragable Array Formula.
The results table can be dragged anywhere on the sheet, but can't start in Column A,
It would be better on a seperate sheet.

Add to the subjects or students in the data table as required, each student should have an unique ID number.

There should be a non-array equivalent to the formula, which would be preferable, but I can't get my head around it at the moment.

It's Saturday night and my dinner is going flat ... ...

[MarvinP]

Ok guys,

For all those afraid of Pivot Tables try this solution.

Click anywhere in the data (say in B4). Then click on the Data Tab and on the Filter Icon. This makes it a table.
Now click on any number in the Math Column and then on the Z->A sort Icon just to the left of the Filter Icon.
Copy and paste the names to another section of your workbook.
Do the same for the English and Spanish names.

No formulas, no Pivot tables, no dynamic named ranges, no Arrays....

How hard should the OP work to get the answer? How hard should we work to let him/her save a few mouse clicks?

[jgray]

Sorry so late in responding. Thanks for all of the assistance! Okay, so, the example I gave was clearly a bit simpler then the actual sheet I will be using. I have tried your formula, but for some reason in my sheet it is not returning the values in the order of Large to Small. Formula is in D15-D29, and corresponding values are in F15-529. Can Ignore Columns G & H. ONA VC Dashboard_TEMPLATE_Final_5-7-2012.xlsm

[jason.b75]

That suggestion was based on your original example, it doesn't work with decimal values, only integers, you would need to try one of the other methods.

[jgray]

Ok. Thanks.

[Marcol]

Your latest posted sample is a table with nothing but #REF! errors ...

Could you post again with the values, no formulae, in F15:H29 and the expected results in D15:D29?

[jgray]

Sorry so late in responding. Thanks for all of the assistance! Okay, so, the example I gave was clearly a bit simpler then the actual sheet I will be using. I have tried a number of the solutions but none are working quite right.

It needs to be a formula, because what is actually occuring in this sheet is I am exporting a large number of "metrics" from a network-analysis software program into a .csv file, which I then copy and paste into a sheet in a larger excel book. One of the sheets in the book is a "dashboard" which displays the data in a more usable format. So, I need a formula that will pull the top 10 actors in the network for a specific metric. Problem is, the network analysis software does not always export the same metric in the same column.... which is why I need a formula that searches for the right metric (column headers), and returns the names of the highest scoring actors in the network (row labels). I have attached a more simplified (but more realistic version of what I am talking about).


THanks again everyone!


ONA VC Dashboard_TEMPLATE_Final_5-7-2012.xlsm

[jgray]

Hmmm. Thats odd. Here is the spreadsheet again. Also, here are the formulas for each of the columns:
F, =(INDEX('ORA - VC - All Actors'!$C$2:$QS$249,MATCH(D15,'ORA - VC - All Actors'!$B$2:$B$249,0),MATCH(F$14,'ORA - VC - All Actors'!$C$1:$QS$1,0)))*100
G, =(INDEX('ORA - VC - All Actors'!$C$2:$QS$249,MATCH($D15,'ORA - VC - All Actors'!$B$2:$B$249,0),MATCH(G$14,'ORA - VC - All Actors'!$C$1:$QS$1,0)))
H, =(INDEX('ORA - VC - All Actors'!$C$2:$QS$249,MATCH($D15,'ORA - VC - All Actors'!$B$2:$B$249,0),MATCH(H$14,'ORA - VC - All Actors'!$C$1:$QS$1,0)))

I need the values (text) in column D to be the respective row labels for the first 10 greatest in-degree values.