Merging two lists, finding and keeping only duplicates

**SilverdaleJB** · 10-22-2010, 08:06 PM

Hi. I have a problem I'm trying to solve. I am merging two lists. Both are lists of names. There are some people on both lists. I want to eliminate the records of the people who were only listed in one of the original two lists and be left only with the list of names who are common to both lists.

Each person on both lists has an ID number that I can use to compare. But while Excel seems to make it easy to eliminate duplicates, I want to do the opposite. I want to eliminate the records of people who are not on both lists and keep only the diplicate records in the spreadsheet.

The only way I've been able to figure out how to do this is to go through manually, mark a column for the duplicates, then sort on that column to collect all the duplicate names and delete the rest. This is very time consuming with a list that contains thousands of names.

Does anyone have a method to identify and segregate the duplicates in a spreadhseet so I can delete the rest?

Thanks.

**Cutter** · 10-22-2010, 08:51 PM

Just a quick thought:

Use helper columns on each list and in each helper column use a formula like:

=IF(COUNTIF(LIST2,list1name)>0,"X","")

and reverse references in other helper column. Then sort each list using the helper columns.

Get rid of anyone without an "X"

Combine the 2 reduced lists and check for duplicates.

**Marcol** · 10-23-2010, 06:07 AM

Try this

With Sheet1
In a spare Column,(assuming your data begins in A2 on both sheets).

=IF(ISNA(MATCH(A2,Sheet2!$A$2:$A$100,0)),"","Match found")

Drag/Fill down

Filter the result for "Match found"

You could add an extra sheet to gather the result if you need to retain the original tables.
Sheet3
In A2

=IF(Sheet1!$E2="Match found",Sheet1!A2,"")

Drag Across as required then Down

Where E2 is the first cell in your helper column on Sheet1.

Sort the result on ID then Filter out or delete the blanks.

Hope this helps