San Diego 2013

Excel Analyze Data

I went back and created one query and stole another one to get the data we'd need for the "Student Data" sheet. The first query deals with everything but the vaccine data.

The two queries are attached below.

What I would do, for simplicity's sake (ok, relative term) is grab the Vaccine data in a separate data dump from Demographics data, then use VLOOKUP to match the two sets of records.

Excel Clean Data

Two SUPER interesting questions I got from our classes.

First one was how to check a student's DOB as of a certain date - say, 9/1/2012. You may recall that we used DATEDIF, the student's DOB (we'll say it was in H2), and today's date, or NOW to get their current age:

Date Differences


The way to do a specific date is to sub out NOW() with the DATEVALUE function, and put that date in quotes:

Now, how do we do it for years and months (aka, 15 years, 10 months)?
=DATEDIF(H2,DATEVALUE("9/1/2012"),"y")&" years, "&
DATEDIF(H2,DATEVALUE("9/1/2012"),"ym")&" months."

The "ym" says, "the remainder months after the years." So you can deduce that years, months, days would be:
=DATEDIF(H2,DATEVALUE("9/1/2012"),"y")&" years, "&
DATEDIF(H2,DATEVALUE("9/1/2012"),"ym")&" months, "&DATEDIF(H2,DATEVALUE("9/1/2012"),"md")&" days"

Repeat a Character

Sometimes, when you submit a file to an agency, the agency requires that the fields be a fixed width. So if you have a name that's 10 characters, you may have to pad the name to get up to 20 characters.

Good news is, the LEN and REPT functions can do that for us. The LEN function, as we know, gets us the number of spaces in a piece of text (or string). The REPT function just says, "repeat this character x number of times."

With that in mind, we say, "repeat this character x number of times, with x being the number of characters it would take to get the name up to 20." Assume that the name is in A2.

=A2&REPT(" ",20-LEN(A2))

If you're concerned about one or more of your names going over 20 characters, you can buy a little insurance:
=LEFT(A2&REPT(" ",20-LEN(A2)),20)

After all, if a person has a first name that's 25 characters, the REPT function just won't come into play; however, you still want just the first 20, so we proceed accordingly.

Excel Advanced Functions

Our VLOOKUP functions were pretty easy; take the Section ID from PGFinalGrade, then use it to lookup the Course Number in the Sections table. That's because we had one common field between the two tables.

Sometimes, though, I don't have one field that's common between the two, or unique. One of our co-workers had a case where in List 1, he had first name, last name, and student number; in the other list, he had first/last in one field, and then photo ID number.

Remember, if you don't have a unique field between the two lists, you can make one.

How? Just use the old composite trick:


Then, you do the VLOOKUP using the composite field as your common field between the two lists. If you need to create a composite list in both lists, not a problem. Just be sure that the composite field is in the first column in the lookup table.

This can be particularly effective when you need to do a VLOOKUP on students, when you have first name, last name, DOB, and grade level in one list, and then maybe ID and name in another.

John Smith the 4th grader born in June is not the same John Smith who's a senior born in May. We know this.

So we create a composite, where we end up with John Smith 4 6/1/1994. Assume the first name is in A2, the last in B2, and the DOB in C2:
=A2&" "&B2&" "&TEXT(C2,"mm/dd/yyyy")

Why TEXT? To force the date to be a date. Not critical for Excel. Easier for us to read, and to verify.