Alphabetize a list

[dogmoo21]

I have a column full of words to the right of a column labeling each of those words with a number (I will show an example of everything at the end). I want the third column to correspond to an alphabetized list of the second column. I want to use a formula, not a macro, to organize this third column.

For example:

1 | gun |
2 | bat |
3 | fit |
4 | con |
5 | act |
6 | hem |
7 | eel |
8 | ire |
9 | dad |

...should become:

1 | gun | 5
2 | bat | 2
3 | fit | 4
4 | con | 9
5 | act | 7
6 | hem | 3
7 | eel | 1
8 | ire | 6
9 | dad | 8

If anyone can help me by providing a formula I could use or somewhere I can look for a formula, that would be greatly appreciated.

 

[martindwilson]

??????????????????????????

 

[tigeravatar]

In your results table, you have "eel" marked as #1 alphabetically, shouldn't "act" be #1? Also, you have "dad" ranked as #8 alphabetically, when it should clearly be before "gun", "fit", "hem", "eel", and ire"

So that's why we're confused

 

[Demo8II]

I don't get it....why is "eel" #1, but "bat" is #2 (in the 3rd column)?

 

[martindwilson]

hmmmmmm maybe
=COUNTIF($B$1:$B$9,"<" & B1)+1

 

[mikerickson]

You could put this CSE formula in C1 (entered with Ctrl-Shift-Enter) and drag it downward.
=100*MOD(SMALL(COUNTIF(B:B,"<="&$B$1:$B$9)+ROW($B$1:$B$9)/100,ROW(A9)),1)