How to combine two excels that have a common column (but aren't in the same order)

Question

I have two excel files, one that I pulled from excel and the other that I pulled from SAP. One file, the larger one, has a column named EmployeeID and the other has a column named EMP_ID, the only difference is the name, the actual employee id within these columns is the same. What I am wanting to do it combine these two files to make a single file; however, for example, Employee ID 'ZZZ' on the first excel file may be on line 1, but on the other file the EMP_ID of 'ZZZ' may be on line 35.

Is there a way to somehow combine these 2 files? Also, sorting by EMP_ID A to Z won't help, because the files don't have the exact same number of entries, so that wouldn't sync them so that each employee is on the same line on each file.

These two files are for reference if it matters. File 1 - http://i.imgur.com/go1S6Ra.png File 2 - http://i.imgur.com/x4vOKIN.png

Answer 1

Here's a pure excel solution. Open file 1 one and go to an empty sheet. Copy and paste the content of file 2.

Go to the first cell in a blank column on sheet two and use vlookup. The formula will look something like this, but you'll have to modify it.

=VLOOKUP(A1,Sheet1.$A$1:$B$419,2,FALSE)

There are four arguments and you will need to play with the first three to get what you want.

A1 Change this to the cell where the employee ID lives in sheet two. So if it's in the third column, this should be C1

Sheet1.$A$1:$B$250 This is where the data from file 1 is. The default name for the first tab is Sheet1 but it might be something different depending on how you opened the file. The second part is the data range. Top left cell and bottom right cell. So if you have 5 columns in sheet one and 1000 rows it will be $A$1:$E$1000 (I'll explain the dollar signs at the end)

1 The third argument how many columns the target data is to the right of the employee ID. So if your columns are ID, First Name, Last Name, putting 3 here would get you the last name.

FALSE, this controls excel's matching behaviour. TRUE looks for close matches, but really it just makes thing unpredictable.

Once the search is working and you're looking up what you want, fill the formula in for the rest of the column. You will get an error if the ID you're trying to lookup isn't in Sheet1, so make sure you tweak the formula on one that exists. If you need to look up multiple things (e.g. First Name and Last Name), just drag the formula to the right and put in a different number for the third argument.

So what's the deal with the dollar signs? This affects what happens when you drag the formula down or over into new cells. If you write this:

=VLOOKUP(A1,Sheet1.A1:B100,2,FALSE)

and drag it down one cell you'll get this:

=VLOOKUP(A2,Sheet1.A2:B101,2,FALSE)

All the row coordinates go up by one. If you dragged it over instead, all of the column coordinates would go up by one, like this:

=VLOOKUP(B1,Sheet1.B1:C100,2,FALSE)

Dollar signs freeze them in place, so the range doesn't drift. You want the search value to keep moving because every line should be looking up the next employee ID, but you don't want the range to move.