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

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?

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


"rinks" <rinks @discussions.microsoft.com wrote in message
...
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?

Hi

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"rinks" <rinks @discussions.microsoft.com wrote in message ...
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?

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


"Ron de Bruin" wrote:

Hi

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"rinks" <rinks @discussions.microsoft.com wrote in message ...
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?

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


"Dave R." wrote:

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


"rinks" <rinks @discussions.microsoft.com wrote in message
...
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?

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"rinks" wrote in message ...
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


"Ron de Bruin" wrote:

Hi

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"rinks" <rinks @discussions.microsoft.com wrote in message ...
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?

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

"Ron de Bruin" wrote:

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"rinks" wrote in message ...
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


"Ron de Bruin" wrote:

Hi

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"rinks" <rinks @discussions.microsoft.com wrote in message ...
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?

Yes

You can change the formula

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

--
Regards Ron de Bruin
http://www.rondebruin.nl


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

"Ron de Bruin" wrote:

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"rinks" wrote in message ...
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


"Ron de Bruin" wrote:

Hi

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"rinks" <rinks @discussions.microsoft.com wrote in message ...
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?

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
Yes

You can change the formula

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

--
Regards Ron de Bruin
http://www.rondebruin.nl


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

"Ron de Bruin" wrote:

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"rinks" wrote in message ...
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


"Ron de Bruin" wrote:

Hi

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"rinks" <rinks @discussions.microsoft.com wrote in message ...
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?

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

--
Regards
Frank Kabel
Frankfurt, Germany

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


"Dave R." wrote:

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


"rinks" <rinks @discussions.microsoft.com wrote in message
...
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?

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$2 00)*(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.