asked on

How to get rid of DUPLICATES in EXCEL ?

I have several Excel files (well, CSV files actually) ... Is there any way to get rid of "Duplicates" based on the data in 1 or 2 columns (for example, get rid of any multiple Last Name & Address, so that we do not mail Newsletters to the same customer twice) ?

zorvek (Kevin Jones)

What version of Excel are you using?

Kevin

ASKER CERTIFIED SOLUTION

zorvek (Kevin Jones)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dlmille

Here's an excellent Q/A on duplicates, with several approaches highlighted.

https://www.experts-exchange.com/questions/26905372/Highlight-duplicate-Rows-and-filter-undu-filter-and-highlight.html

Dave

SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bleggee

ASKER

Thanks ! You guys Rock !

Brian

zorvek (Kevin Jones)

Please note that the second selected post above is not a solution to the problem. When a value appears more than once then ALL rows with that value will have values of 2 or more or TRUE. The correct formula to use is:

=COUNTIF(A2:A$65536,A1)

in row 1 and copied down. If the first row of data is in row 2 then:

=COUNTIF(A3:A$65536,A2)

Kevin

dlmille

If you use

=countif(A:A,A1) and copy down, all you need to do is filter for >1

if you use

=countif(A:A,A1)>1 and copy down, all you need to do is filter for TRUE

Then delete based on what's filtered...

Works for me...

Dave

dlmille

>>Please note that the second selected post above is not a solution to the problem. When a value appears more than once then ALL rows with that value will have values of 2 or more or TRUE.

Exactly - that's the point. Then, filtering for > 1 or filtering for TRUE (user's choice) then filters to the duplicate rows...

Attached demonstrates veracity of formula...

Cheers,

Dave
example-Duplicates.xlsx

zorvek (Kevin Jones)

The asker wants to delete duplicates only so that they don't send mail to the same address twice. Using your formula both entries will be deleted, not just the duplicate entry, so an address that occurs more than once will not receive any mailings.

Kevin

dlmille

Point taken.