This post is a continuation of an earlier one detailing how to merge datasets in Excel or OpenOffice’s Calc. A reader, Tina, asked “What if you have more than one column to lookup to get the match value?” This post is a response to that question.
A merge of two datasets requires at least one column in each dataset to be in common. The first step is to do exactly that.
Sample
Click on the image to see it larger.
Using the picture to illustrate the problem, on the left-hand side is a list of names and addresses. On the right, a list of names and phone numbers. The goal is to have one list, not two. The problem is that the dataset on the left has two columns related to the names: ‘first name’ and ‘last name’. The dataset on the right has the name information in one column, simply called, ‘name’.
The solution
Create a new column that takes the data from the two columns and combines them into one.
Step-by-step
Click on the image to see it larger.
- =a2 – “a2″ refers to a cell containing a first name.
- =A2&” “& – ‘&” “& means that you want to combine the value from a2 with some text. The text is surrounded by quotes. In this case, the text is nothing but a space.
- =A2&” “&B2 – “b2″ refers to a cell containing a last name.
Click on the image to see it larger.
When this is complete, you can now proceed to using vlookup to implement the merge.
Dollar signs
Do not forget about the use of the dollar signs. If you did forget, check out this earlier post. Basically, using dollar signs will control unwanted shifting when you copy and paste the formula.
Paste special
Do not forget about the use of paste special. If you did forget, check out this earlier post. Basically, your newly merged dataset relies on being able to look up other cells. If you delete those cells, the newly merged dataset will be affected.
Was this exactly what you needed? Did this how-to suck? Did I leave something out? What other spreadsheet how-to’s do you want to see?
One Comment
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.