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.

How to use #N/A to create line plots look more presentable

Let's look at the example below. The table showing rejects (column B), output (column C) and reject percentage (column D). Reject percentage = B2/C2.

Day	Rejects	Output	Reject Parentage
1	5	300	1.67%
2	80	400	20.00%
3	30	350	8.57%
4	0	0	#DIV/0!
5	45	455	9.89%
6	67	340	19.71%
7	43	360	11.94%
8	89	400	22.25%
9	23	320	7.19%
10	56	330	16.97%

Note on the fourth day there was no production. So we get an error  in the reject percentage column. If we plot the reject percentage, We will get,

On day four there is no production and so no rejects. 
The plot is not accurate as it shows reject percentage as zero. 

Ideally the plot should ignore day 4. To achieve this we need to change the formula in column D.

=IF(ISERROR(B2/C2)=TRUE,#N/A,B2/C2)

Now the table looks like,

Day	Rejects	Output	Reject Parentage
1	5	300	1.67%
2	80	400	20.00%
3	30	350	8.57%
4	0	0	#N/A
5	45	455	9.89%
6	67	340	19.71%
7	43	360	11.94%
8	89	400	22.25%
9	23	320	7.19%
10	56	330	16.97%

Now plot the reject percentage again.

Now the plot ignores day 4 and it shows no data point on day 4.

As always love to have your comments. 

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