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 COLUMN and COLUMNS functions.

Lets look at the two functions which sound similar but give different results. COLUMN gives the position of the column and COLUMNS counts the number of columns in a range.

Let's see the syntax.

COLUMN([reference])

COLUMNS(array)

Examples
COLUMN(F2) = 6 
COLUMN() = will give the column position of the cell where the function is.

COLUMNS(F10:I14) = 4






One use of the COLUMN function is with VLOOKUP. 
Consider the example below, A table of outputs of different machines in different days.

Now I want to pull only machine 3 data to another place using VLOOKUP. First I key in Machine 3 in A6 for this example.
I need the values in column I to L to appear in column B to E.

I can key in =VLOOKUP($A6,$H:$L,2,FALSE) in B6, =VLOOKUP($A6,$H:$L,3,FALSE) in C5 and so on. 
But I need to keep changing the col_index_num  for every column. If you are working with a large table it can be tedious work.

Here we can use column function to make it automatic when we drag the function. The formula looks like this,

in B6 =VLOOKUP($A6,$H:$L,COLUMN(I:I)-7,FALSE) 

COLUMN(I:I)-7 = 2

When you drag the function,
in C6 =VLOOKUP($A6,$H:$L,COLUMN(J:J)-7,FALSE)
in D6 =VLOOKUP($A6,$H:$L,COLUMN(K:K)-7,FALSE)
in E6 =VLOOKUP($A6,$H:$L,COLUMN(L:L)-7,FALSE)

You can use COLUMNS(A:G) instead of 7
=VLOOKUP($A6,$H:$L,COLUMN(I:I)-COLUMNS(A:G),FALSE)

Depending on the table location and where you want to pull the data the function for col_index_num will change. 


Please let me know comments/feedback.

How to use CONCATENATE to create TAG or Labels

Prerequisite 

Know how to use "CONCATENATE", "MAX", and "IF" individually. Please refer to Microsoft help for 

Syntax. 

What is concatenation means, please read here, http://en.wikipedia.org/wiki/Concatenation.
Simply put join two or more words.

Possible Usage

1. blogging - For those doing blogging regularly for tagging or labeling

2. Tagging for photo (like when you upload to Photobucket etc...)

The steps (How to do)

1. Key in the tags or labels in a column. Please note that the first raw is not used.

2. In cell A2 key in the formula, =IF(B2="A",MAX(A$1:A1)+1,"")

3. Drag or copy paste the formula all the way (to cover the all the tags or labels)

4. Key in "A" in column B in the corresponding raw you need.

5. In column E, key in =max(A:A)

6. In cell F2 type "1"

7. In cell F3 type =IF($E$1<=F2,"",F2+1)

8. Drag or copy paste the formula all the way (to cover the all the tags or labels)

9. In Cell G2 key in formula =IF(F2="","",VLOOKUP(F2,A:C,3,FALSE))

10. Drag or copy paste the formula all the way (to cover the all the tags or labels)

11. In cell H2 key in =CONCATENATE(G2,",",G3,",",G4,",",G5,",",G6,",",G7,",",G8,",",G9,",",G10,",",G11)

12. copy the content to the blogger or any other site

DONE