We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

How to get rid of DUPLICATES in EXCEL ?

Medium Priority
684 Views
Last Modified: 2012-05-11
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) ?
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2008

Commented:
What version of Excel are you using?

Kevin
CERTIFIED EXPERT
Top Expert 2008
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Most Valuable Expert 2012
Top Expert 2012

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

https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26905372.html

Dave
Most Valuable Expert 2012
Top Expert 2012
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Thanks ! You guys Rock !

Brian
CERTIFIED EXPERT
Top Expert 2008

Commented:
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
Most Valuable Expert 2012
Top Expert 2012

Commented:
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
Most Valuable Expert 2012
Top Expert 2012

Commented:
>>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
CERTIFIED EXPERT
Top Expert 2008

Commented:
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
Most Valuable Expert 2012
Top Expert 2012

Commented:
Point taken.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.