Consolidate two excel files and remove duplicates?
Is there a way to consolidate two excel spreadsheets containing contact information (name, street, city, zip code, etc) for 1000 people in each spreadsheet? And then after consolidating the two spreadsheets of contact information, is it possible to remove the duplicates (if the whole row is exactly the same)?
Answers & Comments
Verified answer
If the data layout exactly the same (columns are in the same order and same number of columns), copy both sets of data to a third sheet so that you won't destroy a data source if something goes wrong.
With both sets of data copied one under the other, remove the second set of column headers if you had copied them too. You only need one set.
In Excel 2007, there is a feature called "Remove Duplicates". Select all your data, click on the Data tab and click on Remove Duplicates. All duplicates will be removed for you.
Then if this is what you want to keep, save it, back it up and then get rid of the other two data sets that are no longer needed.
Consolidate Excel Files
You could write a macro but whenever I want to accomplish this type of task, I do the following. It is assumed that both databases have the same columns in the same order.
1. Highlight and copy the data of one spreadsheet and paste it onto the next blank cell in column A of the other one. So if you have 1000 rows in each, you'll now have 2000 rows. Now we need to remove the duplicates.
2. Let's say you have 6 columns of data and 2000 rows plus a header row. In cell G2, write a formula that concatenates the 6 columns of data for that row. =CONCATENATE(A2,B2,C2,D2,E2,F2)
3. Copy that cell and paste it into all remaining rows of the database in that column, say G3..G2001.
4. Sort the new database (A2..G2001) by column G.
5. Now in cell H3, write a formula that compares G3 with G2 and if there's a duplicate, puts an "X" in that cell, otherwise puts a blank in that cell. =IF(G3=G2,"X","").
6. Copy that cell and paste it into all remaining rows of the database in that column, say H4..H2001.
7. Highlight column H and do a copy and paste values.
8. Sort the new database (A2..H2001) by column H. When you do that, all your duplicates, i.e., those with an "X" in column H, will be at the bottom.
9. Delete all rows with an "X" in column H.
10. Delete columns G & H.
11. Save (or SaveAs) your newly created combined database with no duplicates.