?
Solved

How to get rid of DUPLICATES in EXCEL ?

Posted on 2011-04-22
10
Medium Priority
?
588 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) ?
0
Comment
Question by:bleggee
  • 5
  • 4
10 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35450389
What version of Excel are you using?

Kevin
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 1000 total points
ID: 35450393
The steps below illustrate how to filter a table's records so that only unique records are visible, where uniqueness is defined by one or more contiguous key columns. Additional steps are provided to remove the duplicate records from the table permanently. These steps are for Excel 2003 and earlier. For Excel 2007 see the section "Excel 2007" below.

To create a list of unique records based on one or more key columns, first sort the table so that the desired unique records appear first in each same key sequence. If more than one column is to be used to define each record's uniqueness but they are not adjacent to each other, move them such that they are in consecutive order. Follow these steps to hide the duplicate records.

Select the key columns including the headers and choose the menu command Data->Filter->Advanced Filter.

Check "Unique records only" and click OK.

The table can now be viewed or copied to another location. To remove the duplicates records from the table permanently, do the following steps.

Choose the menu command Edit->Office Clipboard to display the Office Clipboard.

Select the entire table excluding the header row and press CTRL+C to copy them to the clipboard.

Choose the menu command Data-Filter->Show All.

Select the entire table excluding the header row and press DELETE.

In the Office Clipboard click the dropdown next to the top entry and select Paste.

The resulting table is just the filtered records.

Excel 2007

To remove duplicates in Excel 2007 and later, select the table, navigate to the Data tab, find the Data Tools group, click Remove Duplicates, select the columns to use to identify duplicate rows, and click OK.

Kevin
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35451585
Here's an excellent Q/A on duplicates, with several approaches highlighted.

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

Dave
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 1000 total points
ID: 35451775
And here's my favorite.  Let's assume the "key" for duplicate hunting is in column a.

Put this formula in a column, and drag down:

=COUNTIF(A:A,A1)


This will give you the # of duplicates for each row.

=COUNTIF(A:A,A1) > 1 will return TRUE/FALSE for duplicate, on a row-by-row basis.

So, if you use autofilter, after having created this column of data, you can filter for > 1 or for TRUE (in the second example) and then select all records and hit the delete key to remove those duplicates.

Dave.
0
 
LVL 1

Author Comment

by:bleggee
ID: 35453700
Thanks ! You guys Rock !

Brian
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35453827
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
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35453890
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
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35453909
>>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
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35453912
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
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35454080
Point taken.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question