Google Answers Logo
View Question
Q: De-Duping Records in Excel ( Answered 4 out of 5 stars,   0 Comments )
Subject: De-Duping Records in Excel
Category: Computers > Software
Asked by: cecil69-ga
List Price: $20.00
Posted: 19 Oct 2002 11:03 PDT
Expires: 18 Nov 2002 10:03 PST
Question ID: 85184
How can one identify and remove duplicate records in Excel without
using another application?  For example, we have 2 files we will merge
into one file.  Both files have 3 fields with identical information, 1
field with different information.  We want to identify those records
with the identical 3 fields and delete them automatically.  We need to
do this without exporting the data into Access or any other
application.  I believe the answer exists in the Advanced Filter
function, but I can't figure out what to use for the criteria and data
ranges.  Thanks.
Subject: Re: De-Duping Records in Excel
Answered By: blinkwilliams-ga on 19 Oct 2002 12:08 PDT
Rated:4 out of 5 stars
Hello and thanks for the question. 

Here is a quick and easy way to dedupe records in Excel. 

1. Make sure that the records are sorted so that identical records
follow one another. Sort ascending by the fields that are repeated.
The "sort" command can be found under "Data" on the main menu bar.

For example, if field1 contains repeating information then sort by
field1 ascending.  Afterwards all the records with identical values
for that field will be grouped together something like this:


2. Let us assume that your first record is in row1. Go to the first
empty column of your first record.  I will call this column 'field2'.
In this cell (record1 x field2) type the number 1. Now go to the cell
directly below it (record2 x field2) and type the following formula:
=IF(A2=A1,0,1).  The cell references "A1" and "A2" will have to be
modified to fit your spreadsheet but basically you want to compare the
field1 value in row2 to the field1 value in row1. The formula says: if
they are identical return a 0, otherwise return a 1. Once you have
entered this formula copy it down for all records. Since it is a
formula, you only need to type it once and then copy it down (either
by dragging the cell down or by double-clicking the box in the lower
right hand corner of the cell).  After you have completed this, your
spreadsheet should look something like this:

apple  1
apple  0
apple  0
orange 1
orange 0
pear   1
pear   0
banana 1
banana 0

Notice that all but the first of record has a field2 value of 0. 

3. Now select the column in which you entered the formula.  Select
'Copy' on the 'Edit' menu.  Then select 'Edit'-->'paste special'.
Select 'values' and click ok. This step is important so that the
formulas don't update after step 4.

4.  Now all you have to do is sort your spreadsheet by field2
descending, the field in which you entered a formula. This will place
all the repeaters at the bottom of the spreadsheet.  In the example I
am using, your results will look like this:

apple	1
orange	1
pear	1
banana	1
apple	0
apple	0
orange	0
pear	0
banana	0

You can then delete all the records with a value of 0 for field2. Now
you have a deduped list of records:

apple	1
orange	1
pear	1
banana	1

It is always a good idea to make a backup version of the original file
before you do any sorting or deleting.

I hope that helps.  Please ask for additional clarification if needed.

Best of luck,

cecil69-ga rated this answer:4 out of 5 stars
I haven't personally used it, but my co-worker has and says it's good.
 He's shocked he didn't think of it, but that's what Google Answers is
for, right?

There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy