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.

How to use INDEX function - part one

We have seen how to use VLOOKUP in the earlier posts,

http://newexceltips.blogspot.sg/2012/07/how-to-use-column-and-columns-functions.html and 

http://newexceltips.blogspot.sg/2012/05/how-to-use-concatenate-to-create-tag-or.html.

In the same way we can use HLOOkUP (vertical and horizontal).


But if we need to use vertical and horizontal look up at the same time, we need to use INDEX. 
Lets say we have table like below,

and say we need to make a table like below,

In the cell J3, type =INDEX( and choose 

As you can see we need an array (table), row number and column number. To find corresponding row number and column number we need to use Formula "MATCH".

for example 

Row number MATCH(H3,A3:A11,0)
Column number MATCH(I3,B2:E2,0)

Lets look how the formula looks like,

Note the row number and column number needed are of the array (table) not of the worksheet.


So here is the formula,
=INDEX(B3:E11,MATCH(H3,A3:A11,0),MATCH(I3,B2:E2,0))

As usual we need to lock the cells.
=INDEX($B$3:$E$11,MATCH($H3,$A$3:$A$11,0),MATCH($I3,$B$2:$E$2,0))

Then drag the the formula to the cells as needed.



We can also use as array formula. 
=INDEX($B$3:$E$11,MATCH(H3:H5,$A$3:$A$11,0),MATCH(I3:J5,$B$2:$E$2,0))
Remember to use Shift+Ctrl+Enter

If we are expecting wrong Machine or Data (accidental or otherwise) then we can add a 

IFERROR (Excel 2007 onwards).
=IFERROR(INDEX($B$3:$E$11,MATCH($H6,$A$3:$A$11,0),MATCH($I6,$B$2:$E$2,0)),"NO VALUE")

or ISERROR (Old versions of Excel)
=IF(ISERROR(INDEX($B$3:$E$11,MATCH($H6,$A$3:$A$11,0),MATCH($I6,$B$2:$E$2,0)))=TRUE,"NO VALUE",INDEX($B$3:$E$11,MATCH($H6,$A$3:$A$11,0),MATCH($I6,$B$2:$E$2,0)))

  

That's it. Your comments are always welcome.