Appending data in Excel

One of the primary reasons to have a database is to have data in various tables.  A simple example is a database with a people table – a list of your donors – and a donations table.  This allows for there to be a one to many relationship between the donors and donations.  After all, a good donor will generally donate multiple times.  By using a key in the people table, each donation is marked with that same key – e.g., all donations from donor 12398 have 12398 in their donor field and are associated with that record in the people table.

Excel can’t do this.  As a flat file, all you can represent are one-to-one relationships.  If you had to have multiple gifts in Excel, you would have duplicate donor records for each instance of the gift.

But you can append data to a record in Excel.

A while back, I wanted to do some homemade modeling on a file to add sex in as a variable (male/female in this case, to simplify the model).  Unfortunately, I didn’t have a list of who was male or female in this particular file, so I used US census records to determine how many males and females there were with a given first name.  This led to the odd conclusion that someone who had a name that 60 females and 40 males have in the US is a 60% woman.  Thank goodness I was using this for modeling and not for assigning Mr. or Ms.

By the way, if you were wondering if there were any perfect half-female, half-male names, there were three: Ariel, Hong, and Kris.  Sydney, Robbie, Frankie, and Kerry were almost androgynous, just missing the cut.  The Excel sheet is here if you are curious.

To complete the model, I had to put the percentage of femaleness with each record.  I used a somewhat fiddly function called VLOOKUP.

VLOOKUP tells Excel to look through a bunch of rows to find a particular value in the left column of an array, find it, and return another column in that array.  To wit:

=VLOOKUP(A1, ‘[Male v Female first names.xlsx]Sheet1’!$A:$B,2,FALSE)

This assumes that A1 is the first name you want to look up.  This then goes over to the document called Male v Female first names and looks at the first two columns, A & B.  It searches for the name in column A.  If it finds it, it returns back the value in B next to that value.  So, if A1 is Hong, it finds Hong in the array, sees the value next to that is 50%, and puts it in the cell in question.  The FALSE at the end just means you want an exact match, rather than the closest one.

It is by no means as pretty or efficient as a SQL JOIN statement.  In fact, if you have a lot of these to do, as I did, copy and paste it for the full column, then get yourself some tea and a crossword puzzle while Excel labors like a hamster on a wheel.  But, on the plus side, you don’t have to learn SQL.  You still should, and we’ll cover SQL for direct marketers at some point, but you can’t learn everything immediately.

Appending data in Excel

Please share your thoughts.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s