Merge two excel files

[Tom Lager]

I have two excel files with the same cell layout. Some of the
entries in the first file are identical to the second file. How do I merge
the two files so that the resulting file has no duplicates? Thanks in
advance. Tom

 

[Bernie Deitrick]

Tom,

Copy the entries from the second file into the first, below the other
entries (don't copy the header row). Then save the file under a new name.

I'm going to assume that your data starts in cell A2, with headers in row 1,
and extends down the page.

In a column to the right of the data table, enter a formula like this:

=SUMPRODUCT(($A$2:A2=A2)*($B$2:B2=B2)*($C$2:C2=C2))

If you have more than three columns, extend the formula like so (for four
columns):
=SUMPRODUCT(($A$2:A2=A2)*($B$2:B2=B2)*($C$2:C2=C2)*($D$22=D2))

I hope you can see the pattern.

Then copy that cell down to match your new, large data table.

Copy the column of formulas, paste as values, then sort the entire table
based on that column. Delete any row where that value is not equal to 1.

HTH,
Bernie
MS Excel MVP

 

[Tom Lager]

Tom,

Copy the entries from the second file into the first, below the other
entries (don't copy the header row). Then save the file under a new
name.

I'm going to assume that your data starts in cell A2, with headers in
row 1, and extends down the page.

In a column to the right of the data table, enter a formula like this:

=SUMPRODUCT(($A$2:A2=A2)*($B$2:B2=B2)*($C$2:C2=C2))

If you have more than three columns, extend the formula like so (for
four columns):
=SUMPRODUCT(($A$2:A2=A2)*($B$2:B2=B2)*($C$2:C2=C2)*($D$22=D2))

I hope you can see the pattern.

Then copy that cell down to match your new, large data table.

Copy the column of formulas, paste as values, then sort the entire
table based on that column. Delete any row where that value is not
equal to 1.

HTH,
Bernie
MS Excel MVP

Thanks Bernie. I'll give it a try.