Microsoft Excel 2003 - Merge by Column

Question

I'm trying to merge two excel files, here is the structure of the files:

[A] [B]

I want to add the column B from one file to another as column C, but the thing is that they have some rows under A that are the same in each file but some rows that are unique to each file. What I need is for them to be merged according to the A column entry. For example:

File 1:  
A   | B  
One | 1  
Two | 2

File 2:  
A    | B  
Four | IV  
One  | I

Merge result file:
A    | B | C  
One  | 1 | I  
Two  | 2 |   
Four |   | IV

So if they share a similar A entry then when the corresponding B entry from one file is added as the C entry of another it should be in the row of the shared A entry. Otherwise if the entry being added doesn't share it's corresponding A entry with the destination file it should be added as a new row with a blank B entry.

Now in actuality column A is actually 3 columns, I was just using one column as an example. I REALLY need it to treat the first 3 column entries as one entry and compare it that way. So its only considered a match if both files have a row where the first 3 column entries are all the same. Otherwise it's not a match.

What's the easiest way to do this?

Answer 1

First, you need to make a sheet that contains all of the values that you want included from the A columns of each original sheet. This is an easy copy-and-paste operation, which you will want to clean up using "Remove Duplicates".

Let's presume you'll put this in column A of your new sheet, as you've described.

Then, you need to have the new sheet pull the column B values from your originals into columns B and C of the new. VLOOKUP can be made to work between different spreadsheets, even if they exist in completely separate files. I'll give some examples below.

For all of the below examples, I'm presuming the following: - Row 1 is for headers - Column A in the new sheet is a merge of the A columns from the originals - Column B in the new sheet is intended to include items from column B of File 1 - Column C in the new sheet is intended to include items from column B of File 2

For naming sheets/files, I'll use the following conventions: - "Result" will be the name of the spreadsheet you're looking to create - "Source1" is data from "File 1" as described in your question - "Source2" is data from "File 2" as described in your question

The following VLOOKUP is for cell B2 in "Result". This formula presumes your "Source" files are separate workbooks stored in your desktop folder, your username is "Me", and you're running Windows Vista or newer. This also presumes you have not renamed any of the sheets in the source workbooks (keeping defaults of Sheet1, Sheet2, etc.).

=VLOOKUP(A2,'C:\Users\Me\Desktop\[Source1.xlsx]Sheet1'!A:B,2,FALSE)

For cell C2 in "Result", you use the exact same formula but change [Source1.xlsx] to [Source2.xlsx]. To finish off the sheet, copy B2 and C2 all the way down their respective columns.

If you want to later break the relationships between the files so that your "Result" sheet can stand independent of the source sheets, just do a copy/paste of "Values Only" on columns A:C of that sheet.

Alternately, you could have all three sheets in one workbook. This makes for a bit of a cleaner formula since you don't need to specify the source file name. Using the naming convention stated above for the sheet names, the formula for B2 would be:

=VLOOKUP(A2,Source1!A:B,2,FALSE)

Again, the formula for C2 would be the same but replace Source1 with Source2. If you later want to remove the source sheets, you'll have to do a copy/paste of "Values Only" as described earlier in order to retain the data you want in "Result".

There is a small caveat to this. If VLOOKUP looks for data and does not find it, you'll get one of those ugly #N/A messages in the cell. You can get around this with IFERROR. Here's an example, using the last formula above as a base:

=IFERROR(VLOOKUP(A2,Source1!A:B,2,FALSE),"")

This essentially says, "If the VLOOKUP returns an error, make this cell's value blank. Otherwise, display the result of the VLOOKUP."

If you run into trouble, I suggest consulting the Help documents and/or Google for "Remove Duplicates", "VLOOKUP", "Paste Special", and/or "IFERROR" - whichever part you're having a problem with.

NOTE: I've tested these functions in Excel 2010, and have experience using them in Excel 2007 as well. I'm not sure if all of these features are available in Excel 2003 or not. I strongly recommend you upgrade to Office 2007 or later in the near-ish future. Support for Office 2003 will be ending in 2014.