A student in today's class asked me about photos and the MATCH function. The question was, if we have a list of students, some of whom have had their photo taken, and some who have not, how can we get the ones with pictures to show the message "No Photo," and the students with a photo, show the photo instead of the row number.

The formula is: =IF(ISNA(MATCH(A2,Photos!$A$1:$A$427,FALSE)),"No Photo",VLOOKUP(A2,Photos!$A$1:$D$427,3,FALSE)

The components:

The IF function: if condition, then message, otherwise alternate message

The ISNA function is a test - if I do a lookup for a value, will I get the #N/A error? If so, then ISNA is true.

In other words, if I do a match for the student's student number in the Photos worksheet, and I get #N/A, I want the "No Photo" message to show up.

If I can find the student, then I want to return the photo number.

Truly Unique User Names

One of the questions I got in Open Session related to how to create truly unique usernames. My customer had the first name in column A, the last name in column B, and the middle initial in column C.

I could do my 4-1-1 trick, which would be =LEFT(B2,4)&LEFT(A2,1)&C2, but that's no guarantee of complete uniqueness.

What is? In the adjacent column to the username (let's say it's column D), add a little COUNTIF magic; assume that the last username is in row 224:

=COUNTIF(D2:D$224,D2)

(Assume that one of the usernames is my old Pearson username - lillgg, or Geoffrey G Lilley. Imagine further that Grace G Lilley also works for my school.)

Notice the dollar sign is ONLY after 224, the last row. The net result is that the first time I find the username "lillgg", I see the number 1. If I find it a second time, I see a "2" in that cell.

Almost there.

The customer I was working with wanted the second username (the second lillgg) to be "lillgg02" but the first one to be just "lillgg." So we want an IF function, I think.

The D column had the username, so the COUNTIF function is in column E. In column F, use the IF function: =IF(E1>1,D1&"0"&E1,D1)

In other words, if it's the second time this username shows up, add a zero and the number (2,3) to the end; otherwise, just leave it be.

Example below:

LastFirst

User Name

Abbot, Julie D

Abbot

Julie

D

=LOWER(LEFT(B2,4)&LEFT(C2,1)&D2)

=COUNTIF(E2:E$123,E2)

=IF(F2>1,E2&"0"&F2,E2)

Abbott, James D

Abbott

James

D

=LOWER(LEFT(B3,4)&LEFT(C3,1)&D3)

=COUNTIF(E3:E$123,E3)

=IF(F3>1,E3&"0"&F3,E3)

Abram, Michael W

Abram

Michael

W

=LOWER(LEFT(B4,4)&LEFT(C4,1)&D4)

=COUNTIF(E4:E$123,E4)

=IF(F4>1,E4&"0"&F4,E4)

Accatino, Steve P

Accatino

Steve

P

=LOWER(LEFT(B5,4)&LEFT(C5,1)&D5)

=COUNTIF(E5:E$123,E5)

=IF(F5>1,E5&"0"&F5,E5)

Adair, Amanda L

Adair

Amanda

L

=LOWER(LEFT(B6,4)&LEFT(C6,1)&D6)

=COUNTIF(E6:E$123,E6)

=IF(F6>1,E6&"0"&F6,E6)

Adams, Mark K

Adams

Mark

K

=LOWER(LEFT(B7,4)&LEFT(C7,1)&D7)

=COUNTIF(E7:E$123,E7)

=IF(F7>1,E7&"0"&F7,E7)

Allen, Sarah D

Allen

Sarah

D

=LOWER(LEFT(B8,4)&LEFT(C8,1)&D8)

=COUNTIF(E8:E$123,E8)

=IF(F8>1,E8&"0"&F8,E8)

Arnold, Jim S

Arnold

Jim

S

=LOWER(LEFT(B9,4)&LEFT(C9,1)&D9)

=COUNTIF(E9:E$123,E9)

=IF(F9>1,E9&"0"&F9,E9)

Arnold, Paul B

Arnold

Paul

B

=LOWER(LEFT(B10,4)&LEFT(C10,1)&D10)

=COUNTIF(E10:E$123,E10)

=IF(F10>1,E10&"0"&F10,E10)

Splitting Dates

Several of you who were transitioning over from SASI had a similar question - how to take a date that was formatted yyyy-mm-dd (2010-06-21) and turn it into a date that was readable by Excel?

There's two ways to do it.

The first way I could think of is three text functions; assume the dates are in column A:
=MID(A2,5,2)&"/"&RIGHT(A2,2)&"/"&LEFT(A2,4)&

In other words, take out the month first. The month is in the middle, so we use the MID function. Start at the fifth character, and get two characters, the fifth and the sixth. Add a slash (or dash). Get the day by using the RIGHT function, and get the two rightmost characters. Add another slash. Get the year by taking the first four characters.

Alternatively, you could use Text to Columns. Instead of Delimited, though, you might end up having to use Fixed Width, if there's no dash or slash to break up the pieces. Just use Fixed Width, and then break the date up at the year and month, respectively:

Then, click Finish, and your date will be broken up.

## Photos Not Found

A student in today's class asked me about photos and the MATCH function. The question was, if we have a list of students, some of whom have had their photo taken, and some who have not, how can we get the ones with pictures to show the message "No Photo," and the students with a photo, show the photo instead of the row number.

The formula is:

=IF(ISNA(MATCH(A2,Photos!$A$1:$A$427,FALSE)),"No Photo",VLOOKUP(A2,Photos!$A$1:$D$427,3,FALSE)

The components:

The IF function: if condition, then message, otherwise alternate message

The ISNA function is a test - if I do a lookup for a value, will I get the #N/A error? If so, then ISNA is true.

In other words, if I do a match for the student's student number in the Photos worksheet, and I get #N/A, I want the "No Photo" message to show up.

If I can find the student, then I want to return the photo number.

## Truly Unique User Names

One of the questions I got in Open Session related to how to create truly unique usernames. My customer had the first name in column A, the last name in column B, and the middle initial in column C.I could do my 4-1-1 trick, which would be =LEFT(B2,4)&LEFT(A2,1)&C2, but that's no guarantee of complete uniqueness.

What is? In the adjacent column to the username (let's say it's column D), add a little COUNTIF magic; assume that the last username is in row 224:

=COUNTIF(D2:D$224,D2)

(Assume that one of the usernames is my old Pearson username - lillgg, or Geoffrey G Lilley. Imagine further that Grace G Lilley also works for my school.)

Notice the dollar sign is ONLY after 224, the last row. The net result is that the first time I find the username "lillgg", I see the number 1. If I find it a second time, I see a "2" in that cell.

Almost there.

The customer I was working with wanted the second username (the second lillgg) to be "lillgg02" but the first one to be just "lillgg." So we want an IF function, I think.

The D column had the username, so the COUNTIF function is in column E. In column F, use the IF function:

=IF(E1>1,D1&"0"&E1,D1)

In other words, if it's the second time this username shows up, add a zero and the number (2,3) to the end; otherwise, just leave it be.

Example below:

## Splitting Dates

Several of you who were transitioning over from SASI had a similar question - how to take a date that was formatted yyyy-mm-dd (2010-06-21) and turn it into a date that was readable by Excel?

There's two ways to do it.

The first way I could think of is three text functions; assume the dates are in column A:

=MID(A2,5,2)&"/"&RIGHT(A2,2)&"/"&LEFT(A2,4)&

In other words, take out the month first. The month is in the middle, so we use the MID function. Start at the fifth character, and get two characters, the fifth and the sixth. Add a slash (or dash). Get the day by using the RIGHT function, and get the two rightmost characters. Add another slash. Get the year by taking the first four characters.

Alternatively, you could use Text to Columns. Instead of Delimited, though, you might end up having to use Fixed Width, if there's no dash or slash to break up the pieces. Just use Fixed Width, and then break the date up at the year and month, respectively:

Then, click

Finish, and your date will be broken up.To put it back together:

=B2&"/"&C2&"/"&D2