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