Rank and Sort Problem

[HMB]

Excel is not a strong point, but after a string of formulas I'm left with two columns of data that i can't sort automatically as id like. Here is the situation:

I have the following data:
<table style="border-collapse: collapse; width: 240pt;" width="320" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="5"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" width="64" height="20">
</td> <td class="xl64" style="width: 48pt;" width="64">A</td> <td class="xl64" style="width: 48pt;" width="64">B</td> <td class="xl64" style="width: 48pt;" width="64">C</td> <td class="xl64" style="width: 48pt;" width="64">D</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">1</td> <td class="xl64">1</td> <td>Mike</td> <td class="xl64">1</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">2</td> <td class="xl64">2</td> <td>
</td> <td class="xl64">2</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">3</td> <td class="xl64">3</td> <td>John</td> <td class="xl64">3</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">4</td> <td class="xl64">4</td> <td>Bill</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">5</td> <td class="xl64">5</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>

Originally there was 5 names and now they're are only 3, Mike, John and Bill; so in cells B1:B5 two of the cells are blank (there actually a vlookup formula that returned 0). Mike is 1, John is 3, and Bill is 4. However now that two of the names are gone I need to re-rank the 3 names because while Mike is still 1, John in now 2, and Bill is now 3. So in column C I have a standard series of 1 through 3 and in column D I want a formula puts the name Mike in D1, John in D2 and Bill in D3.

This is a simplified situation as there are actually a ton of names and gaps.

Any advice would be much appreciated - Thanks

 

[AlphaFrog]

You could use an Array formula but I would suggest you create a helper column to help you calculate because you have "tons" of names.

In E1 put this and copy it down column E (you could use any empty column as a helper column)
=IF(ISBLANK(B1, "", A1)

This will create a column of numbers excluding the numbers where there is no name.

In D1 put this and copy it down column D
=IF(C1>COUNTA(B:B),"",INDEX(B:B,SMALL(E:E,C1)))

 

[AlphaFrog]

One other idea, If you wanted to put this in column A instead of the sequential numbers
=IF(B1="","",COUNTA($B$1:B1))

Then Use this in D1
=IF(C1>COUNTA(B:B),"",INDEX(B:B,MATCH(C1,A:A,0)))

 

[HMB]

AlphaFrog - Thanks - I really appreciate the help. I think your first response is the one I want to go with, however when I try and post your first formula I get an error saying "to many arguments" when I hit enter.

Any thoughts?

 

[HMB]

AlphaFrog - Also, I just tried your second recommendation, seeing that you took the time to post it, and unfortunately it doesn't seem to work. When I drag the formula down in column D and I hit D2 I get #N/A and then when I get to D3 it pulls John.

What I need is D2 to pull John and D3 to pull Bill.

Any help would be great.

 

[AlphaFrog]

I realized that the blank cells in between the names in column B may not be truly blank. You're probably using some formula to return the name or ""

Since you said you want to use my 1st solution, try putting this in column E instead...
=IF(LEN(B1)=0, "", A1)

And use this in D1
=IF(C1>COUNTIF(E:E,">0"),"",INDEX(B:B,SMALL(E:E,C1)))

 

[HMB]

Alpha you're on it...that worked perfect. Thanks so much.

On a final note, I'm going to have to start on row 6 so I can add some headers and whatnot, however if I add 5 rows it throws off the linking in column D. Any suggestions?

I honestly can't even fully understand what your formulas are doing - that's some slick ****. Thank you again.

 

[AlphaFrog]

Put this in D6

=IF(C6>COUNTIF($E$6:$E$1000,">0"),"",INDEX($B$6:$B$1000,SMALL($E$6:$E$1000,C6)))


This works starting at row 6 down to row 1000. You can make the 1000 as big as you need.

 

[HMB]

I can follow that change - once again it worked perfect.

Thank you.