Rank table and return values to the left in alphabetical order

Hi folks,

I wonder if you can help me. I've attached my sample data - Column A,B and C are a pivot table and Column D is an extra helper column I've put in.

I have a list of people, a number and the rank of that number.

What i'm trying to is to write a formula that searches for Ranks 1 through to 10 and returns the name of the person who features in that position.

HOWEVER, there are a number of people in the list who have 0 for their number, when there are more than 10 people who have zero, I want to return the 10 people who have a 0 as their number and are in alphabetical order.

I found this tasty little number to get them in alphabetical order, but this is very much the cart before the horse..

=(CODE(LEFT(UPPER(LEFT(A2,1)),1))-64)+((CODE(LEFT(UPPER(MID(A2,2,1)),1))-64)/100)

eg. Avninder would return 1.22 (1 for the letter "A" and .22 for the letter "V") allowing me to then rank the names in alphabetical order.

The challenge I have is then returning the names for ranked 1-10 as in this example, Place 1 actually contains 11 people - next time I run the report, place 1 may only contain 1 person, whereas place 2 may contain more and I need to write something that future proofs this (I run it every week).

I hope I've been clear and many many many thanks in advance!

sample.xlsx

I'm not exactly sure what output you're looking for rank-wise, but you could try this formula to rank them effectively in alphabetical order. Place in cell E2 and drag down:

Please Login or Register  to view this content.

From here you could filter your data from smallest to largest to return a stacked list of names in descending alphabetical order. Hope this helps.

thats perfect, did the trick perfeclty! Thanks for your help