Sort values on Col A based on values in Col B

**holmes123** · 07-26-2012, 04:38 PM

I have a list of names in Column A and a list of scores in Column B. They are unsorted.

In Column C I would like to list the names, sorted by descending score.

My formula is returning #VALUE's, I think because the last few rows in Column A and B contain an empty string (""). (The number of names over time will change over time, but it will never exceed 100.)

How can I edit the formula to account for this? I've tried all sorts of things and can't figure it out. If it helps, I do have a cell that stores the number of non-blank rows in Column A and B.

Formula:

Please Login or Register  to view this content.

**FDibbins** · 07-26-2012, 06:59 PM

to the best of my knowledge, you cannot use large() or rank() on text, only values. perhaps you could upload a sample worksheet with some (dummy?) data for us to look at? that would make it easier to offer some sollutions

---------- Post added at 06:59 PM ---------- Previous post was at 06:57 PM ----------

oops disregard that, i just re-read that you want it sorted by score lol, sorry

**holmes123** · 07-27-2012, 11:43 AM

Found the solution with this excellent video by the ExcelIsFun guru here: http://www.youtube.com/watch?v=OZPluqE2tLM

I've also attached the relevant workbook from his site which shows the formulas.

His example has a list of names in A4:A8, and a list of sales figures in B4:B8. He wants to recreate the same data but sorted by sales figures. Names will go in G11:G15, and sales will go in H11:H15.

1. Get the list of sales figures from B4:B8, and put them in descending order:

=LARGE($B$4:$B$8,ROWS(G$11:G11)) (copy down)

2. Now get the names for each corresponding sales figure:

=INDEX($A$4:$A$8,SMALL(IF($B$4:$B$8=G11,ROW($B$4:$B$8)-ROW($B$4)+1),COUNTIF($G$11:G11,G11)))

Because his data has ties, he uses SMALL(IF ...). If it doesn't have ties, he could use a more simple MATCH function. He explains all of this in the video.