How to delete duplicate records when I merge two lists (deleting .

[Guest]

I am merging two mailing lists (lists A & B). Some of the names from list A
are also in List B. When I merge the two, I want to delete any records that
are duplicated. I want to delete the original record and the duplicate, so an
advanced filter selecting "Unique records only" will not work. What do I do?
Is there an "IF" statement that I can run against the merged list that will
identify and deleted any record that is duplicated?

 

[Dave R.]

You can use a formula like this

=COUNTIF(A$1:A$1000,A1)

next to all your records. Then you can sort them by that column and delete
anything with a "2" next to it.

More info here;

http://www.cpearson.com/excel/duplicat.htm

 

[Ron de Bruin]

Hi

My Add-in have a duplicate option that you can use
http://www.rondebruin.nl/easyfilter.htm

 

[Guest]

That is a great tool, but can it be used when I am searching for duplicates
in data across multiple columns?
Example:
Last Name First Name Address
Smith John 123 Main Street

 

[Guest]

How would I apply this to identify duplicates within data that spans multiple
columns?
Example:

Last Name First Name Address
Smith John 123 Main St
Adams Bill 456 Park St
Smith John 123 Park St

 

[Ron de Bruin]

in data across multiple columns?

No this version one column

But you can use a helper column that will merge
your last and first name and use the duplicate option on that column

=A1&" " & B1

 

[Guest]

Is it possible to merge more than 2 columns? Say 4 or 5 to include Street
Address, City, State, etc...

 

[Ron de Bruin]

Yes

You can change the formula

Or use a function
http://www.mcgimpsey.com/excel/mergedata.html

 

[Ron de Bruin]

And this one
http://www.mcgimpsey.com/excel/udfs/multicat.html

 

[Frank Kabel]

Hi
one way:
- use a helper column. e.g. in d1:
=A1 & "^" & B1 & "^" & C1
and uye this helper column

 

[hrlngrv]

rinks wrote...

How would I apply this to identify duplicates within data that spans multiple
columns?
Example:

Last Name First Name Address
Smith John 123 Main St
Adams Bill 456 Park St
Smith John 123 Park St

....

Many responses to how to do this after the fact. Better to avoid
duplicates in the first place, and you *can* use advanced filters for
that.

If the first list with column headings were in the active worksheet in
A1:C100 and the second list in XYZ!A1:C200, then copy the column
headings to E1:G1 and enter the folllowing formula in I2.

=SUMPRODUCT((A2=XYZ!$A$2:$A$200)*(B2=XYZ!$B$2:$B$200)*(C2=XYZ!$C$2:$C$200))=0

Now select the first list in A1:C100 and run the menu command Data >
Filter > Advanced Filter... . Choose 'Copy to another location', select
I1:I2 as the Criteria range and E1:G1 as the Copy to [range], check
'Unique records only', and click OK. This will put all records from the
first list with no 3-field match in the second list into columns E:G
with the first record in E2:G2.

Then copy the column headings into the row immediately below the last
extracted record in E:G, and run the menu command Data > Filter >
Advanced Filter... . Choose 'Copy to another location', change the List
range to XYZ!A1:C200, clear the entry for the Criteria range (don't use
any criteria), set the Copy to [range] to the newly copied row of
column headings in E:G, check 'Unique records only', and click OK. This
will add the 3-column distinct records from the second list to the
merged list in E:G. Finally, delete (as in Edit > Delete, move cells
up) the second row of column headings in E:G. What's left in E:G is the
list without 3-column duplicates.