Merging data in a spreadsheet with multiple columns

This post builds upon “How to merge data sets using an Excel or Calc spreadsheet.” A reader, Tina, asked “What if you have more than one column to lookup to get the match value?” This post tries to answer that question.

The key thing about merging data sets is that they MUST share at least one column in common. If this is not the case, then you will have to make it so!

Here is an example of two related data sets.

There is a list of names and street addresses on the left side of this table. On the right side is a list of names and phone numbers. The problem is that we can not merge the two lists, because the name-related columns are not the same.

The first column shows row numbers. The second column shows first names. The third column shows last names. The fourth column contains addresses. The fifth, sixth, and seventh columns are purposely left blank and represent the separation between the two data sets. The eighth column contains names. The ninth column contains phone numbers.

  A B C D E F G H
1 First Name Last Name Address       Name Phone
2 Sly Stone 123 Main St       George Clinton 555-555-6969
3 George Clinton 1 P-Funk Ln       Sly Stone 555-555-1212
4 Stevie Wonder 22 Talking Book Ln       Stevie Wonder 555-555-2323

Continue reading

How to merge data sets using an Excel or Calc spreadsheet

The scenario

Let us imagine two related data sets in Excel or LibreOffice’s Calc. One contains a list of names and corresponding phone numbers, and the other is a list of names and email addresses. The two data sets have a unique id in common (like a Social Security number). We want to merge the two data sets together into a single table. This is a common problem with an easy solution.

Here is an example of two related data sets.

More about this table

There is a list of names and phone numbers on the left side of this table. On the right side is a list of names and email addresses. Our goal is to merge the two lists into one.

The first column shows row numbers. The second column shows ID numbers. The third column shows telephone numbers. The fourth column shows names. The fifth and sixth columns are purposely left blank and represent the separation between the two data sets. The seventh column contains ID numbers. The eighth column contains names. The ninth column contains email addresses.

  A B C D E F G H
1 ID Tel Number Name     ID Name Email
2 01 555-1313 Dennis Osorio     06 Jesse Pinkman capncook@heisenberg.com
3 02 555-4242 Carrie Mathison     09 Tom Haverford tom@entertainment720.com
4 03 555-2323 Saul Berenson     05 Walter White blue@heisenberg.com
5 04 555-5353 King Joffrey     01 Dennis Osorio dposorio@fake.huh
6 05 555-3636 Walter White     10 Andy Dwyer just-the-tip@pawnee.gov
7 06 555-6464 Jesse Pinkman     07 Hank Schrader hank@thefuzz.gov
8 07 555-4747 Hank Schrader     02 Carrie Mathison carrie.mathison@the-cia.gov
9 08 555-7373 Saul Goodman     08 Saul Goodman saul@bettercallsaul.com
10 09 555-5858 Tom Haverford     02 Saul Berenson saul@notevenlefthanded.net
11 10 555-8484 Andy Dwyer     04 King Joffrey no-pain-no-gain@thelannisters.com

Continue reading