Arranging names in alphabetical order and placing them under the alphabet

Lets say we have 50 names and the names to be put under them starting alphabet in alphabetical order.

Below is the sample of names.

Sample of expected results, names under their respective first letter.

OK, lets see how to do this.

In this example names are in worksheet 1.
I am going use another sheet (sheet 2) for the intermediate sorting and worksheet 3 for the final results.

In the intermediate sorting, I will list the names under each alphabets, but not in sorted.

In sheet 2 lets key in Alphabets (A, B, C, ....) in alternate columns as shown below. Make sure to have a empty column before the column with  alphabet A.

In C2 key in =IF(LEFT(Sheet1!$D2,1)=Sheet2!C$1,Sheet1!$D2,"ZZZZZ") H

Lets take a look at the formula and analyse what it does.

The condition for the IF function is LEFT(Sheet1!$D2,1)=Sheet2!C$1. I am just taking the first letter of the name and comparing against the Alphabet, in this case "A".

The result if "TRUE" is the name (in the corresponding row) in Sheet 1. The result if "FALSE" is "ZZZZZ". Why "ZZZZZ"? Let me explain later.

Note:  How to use LEFT command please refer to this post. http://newexceltips.blogspot.sg/2012/06/how-to-use-date-right-and-left-formulas.html

Drag the formula as needed. The result is like shown below.

As you can see the names appear under their respective first alphabet. But there are lots of "ZZZZZ" and the names are not sorted A-Z.

Now I make use of the empty column in-front of every column with names. For example for column C which contains names starting with" A" I use cell B2 to key in the following formula.

=COUNTIF(Sheet2!C$2:C$51,"<="&Sheet2!C2)

This formula will give numbers corresponding to the name in the list. For example if we have name AA, AZ and AD, the corresponding numbers will be 1, 3 and 2. This is exactly what a alphabetical sorting order should be. So "ZZZZZ" will be given max value, in this example 50.

In another sheet you can have Alphabets on one row and use VLOOKUP to pull the names in the sorted order.

The Vlookup command is (in cell B2)

=VLOOKUP(ROW(B2)-ROW(B$1),Sheet2!B:C,2,FALSE)

Notice the error in cell B6 onwards after dragging the formula.
To correct this add a IFERROR formula to VLOOKUP formula.

=IFERROR(VLOOKUP(ROW(B2)-ROW(B$1),Sheet2!B:C,2,FALSE),"")


Now extend the formula to all the alphabets (A-Z) to complete the table.

If any addition of names in the original list will be updated automatically (the formula range should cover the cell).  For example add "Aann". 

Please leave your comments.