Merging data in a spreadsheet with multiple columns

By

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

The solution

Create a new column that takes the data from the two columns and combines them into one.

Step-by-step

A new column added

This is table is similar to the previous one except that the Address column has been moved from column C into column D. Column C is now occupied by a new column titled Name. It will contain a formula.

The first column shows row numbers. The second column shows first names. The third column shows last names. The fourth column contains a formula. The fifth column contains addresses. The 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 Name Address     Name Phone
2 Sly Stone Sly Stone 123 Main St     George Clinton 555-555-6969
3 George Clinton George Clinton 1 P-Funk Ln     Sly Stone 555-555-1212
4 Stevie Wonder Stevie Wonder 22 Talking Book Ln     Stevie Wonder 555-555-2323

Cell C2 contains the following formula: =a2&” “&b2 which contains three parts.

  1. =a2 refers to a cell containing a first name.
  2. &” “& represents the addition of some text. What we are adding is enclosed and separated from the rest of the formula by a pair of & symbols. The text being added is enclosed by a pair of quotation marks. In this example, the text being added consists of a single space.
  3. b2 this is a cell reference, in this case it is a cell containing a last name.
Use vlookup to proceed with merge

This is table is similar to the previous one except that column E is no longer empty. It is now titled Phone Lookup and contains a formula.

The first column shows row numbers. The second column shows first names. The third column shows last names. The fourth column contains names. The fifth column contains addresses. The sixth column contains a formula. The seventh column is purposely left blank and represents 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 Name Address Phone Lookup   Name Phone
2 Sly Stone =a2&” “&b2 123 Main St =vlookup(c2,g$2:h$4,2,0)   George Clinton 555-555-6969
3 George Clinton George Clinton 1 P-Funk Ln 555-555-6969   Sly Stone 555-555-1212
4 Stevie Wonder Stevie Wonder 22 Talking Book Ln 555-555-2323   Stevie Wonder 555-555-2323

Dollar signs

If unsure about the dollar signs, check out this earlier post. Basically, using dollar signs will control for unwanted shifting when copying and pasting the formula.

Paste special

If unsure about the use of paste special, check out this earlier post. Basically, your newly-merged data set relies on being able to look up other cells. If you delete those cells, the newly-merged data set will be affected.

Was this exactly what you needed? Did I leave something out? What other spreadsheet how-to’s do you want to see?

One thought on “Merging data in a spreadsheet with multiple columns”

  1. This helpful – I usually do this long process of copying the fields into a Word doc, converting table to text, then back text to table, and copying back in to excel.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *