Merging Tables in Excel

If you're like me, you regularly come across complicated tasks in Excel that are not easily automated. I'm slowly learning more and more about the more powerful functions available in Excel, and usually with a little bit of researching and work, you can figure out most challenges.

My most recent one was merging two tables, where the column identifying the same person in each table wasn't identical. Each had people that weren't listed on the other. Searching online turned up no walkthroughs, only third-party plugins to Excel that cost in excess of $40. Not wanting to cost my employer money to perform this task, I kept working on it. Finally found a similar solution on this page (credit where credit is due) from an "Anne Troy":

http://forums.techguy.org/business-applications/139190-merging-tables-access.html

Here is the walkthrough, in my own words. Hopefully this is useful to somebody out there - I'm hoping Google picks it up in its search results to help others out there with this same challenge.

Make sure both tables are in the same Workbook in different Sheets.

Step One: Add rows from new sheet that don’t already exist in the Master

1. Sort the Master sheet in ascending order by the identifying column
2. Sort the new sheet in ascending order by the identifying column
3. Define a name for the identifying column in the Master sheet – call it “Master”
4. Add a column to the right of the new sheet
5. Enter this formula, and copy down:
   =VLOOKUP(A2,Master,1,FALSE)
   Where A is the column that contains the identifying value and the first column in the Master area is the column with the same identifying value
6. Sort by the new column so that all #N/A values come together
7. Copy all the #N/A rows with only the identifying value into the Master sheet
8. Delete the new column with the formula result

Step Two: Merge data into Master

1. Re-sort the Master sheet in ascending order by the identifying column
2. Re-sort the new sheet in ascending order by the identifying column
3. Create a new column in the Master for the new data
4. Define a name for the table to be merged into the Master – call it “MyData”
5. Enter this formula in the new column in the Master and copy down:
   =IF(ISNA(VLOOKUP(A2,MyData,2,FALSE)),"",VLOOKUP(A2,MyData,2,FALSE))
   Where A is the column that contains the identifying value in the Master and the second column in the new data sheet contains the data that needs to be merged in
6. Copy all cells, then paste over them using the Paste Special -> Values