# Remove duplicates by comparing two pairs of columns in separate tables

• i have a new post; however, similar to this formula so wanted to just continue. what i'm trying to do now is take the columns A and B, compare to columns D and E (both are LAST and FIRST names), and pull a list of the Unique values. So, I could either eliminate the duplicates (although when i highlight the cells and click eliminate duplicates message is says "no duplicate records found")...or i would think there's probably a similar formula that would compare columns A/B to D/E and eliminate the duplicates while leaving unique records. In the attached spreadsheet, both OLIVA, ADELA and SUAREZ, ADRIANA should be eliminated, leaving the remaining Unique records in columns A and B. uploaded spreadsheet. thx for any assistance.

## Files

• Re: Remove duplicates by comparing two pairs of columns in separate tables

This is not a follow on question. The situation is entirely different (although I can see why you thought it was similar). Always start a new thread unless you are absolutely sure that the question is very closely related to your original question. This time I have moved your post to a new thread. In future the original thread may simply be locked or the new post deleted.

This time you have two separate tables and you are trying to eliminate duplicates based on the two tables. The solution will be somewhat different.

• Re: Remove duplicates by comparing two pairs of columns in separate tables

thank you and my apologies. i appreciate you creating the new thread and will certainly do so in the future, under similar circumstances. i look forward to feedback regarding solutions to my inquiry.

jo

• Re: Remove duplicates by comparing two pairs of columns in separate tables

Your example does not seem illustrate what you are trying to achieve. In your example the list in A and B only contains unique values, and what I take to be 'What you want' in N & O simply contains a copy of A & B.

Can you explain a bit more carefully what you require or upload a Workbook containing data that illustrates the situation.

• Re: Remove duplicates by comparing two pairs of columns in separate tables

in the attached spreadsheet, please disregard any data that is not in columns A, B, D, or E. the remainder of the columns are not relevant. what i'd like to do is a comparison between columns A/B and D/E to see which are duplicate records. in the attached spreadsheet, Suarez, Adriana and Oliva, Adela are duplicates. what i'd like to do is elimate those two names and have a 'results' column that then shows Reddy A, Shah A, and Pelaez A. Dennis.

i hope that makes better sense. seems there would be an easy way to do this. i initially ran the 'eliminate duplicates'; however, it leaves one of the duplicate records and deletes the second. i would like to identify duplicates and delete both, leaving only the 3 names that are unique records (Reddy A, Shah A, and Pelaez A Dennis)

thx.

jo

• Re: Remove duplicates by comparing two pairs of columns in separate tables

Again using an advanced filter, but the formula for the criteria is as follows:

=SUMPRODUCT(--(\$A2&\$B2=\$D\$2:\$D\$3&\$E\$2:\$E\$3))=0

Note that the two concatenated ranges (highlighted in blue) must be the same size and must cover your list that you wish to exclude. Then select the ranges for an advanced filter as normal.

Attached sample Workbook

• Re: Remove duplicates by comparing two pairs of columns in separate tables

formula works perfectly. sorry i made that so complicated or didn't explain it well, but you nailed it. thanks again.

jo

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!