Keeping duplicate records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

There are many postings about deleting duplicate records but how in excel can I find only duplicate records. I have large spreadsheets that I want to find duplicate records maybe within a single column or if any 2 records in any field match and highlight these.
 
Dear Steve
Pay a visit to http://www.cpearson.com/excel/duplicat.htm
you will find some very useful stuff there
-----Original Message-----
There are many postings about deleting duplicate records
Click to expand...
but how in excel can I find only duplicate records. I have
large spreadsheets that I want to find duplicate records
maybe within a single column or if any 2 records in any
field match and highlight these.
 
Try the following steps, which uses Advanced Filter
in a *back-up* copy of your workbook (not the original)

Assume you want to extract the duplicates in the Names
column in Sheet1

Select the Name column
Click Data > Filter > Advanced Filter
(Click OK to the Excel prompt to use the first row as labels)

In the Advanced Filter dialog:
Check Filter the list, in-place
Check Unique values only
Click OK

Select all the "blue colored" filtered rows
(select all the "blue" row headers)

These are the "unique" rows which you do not want

Right-click > Delete rows

Click Data > Filter > Show All

This will reveal the rows with the duplicate Names in Sheet1

--
hth
Max
-----------------------------------------
Please reply in thread

Use xdemechanik <at>yahoo<dot>com for email
----------------------------------------------------------------------------
Steve Masters said:
There are many postings about deleting duplicate records but how in excel
Click to expand...
can I find only duplicate records. I have large spreadsheets that I want to
find duplicate records maybe within a single column or if any 2 records in
any field match and highlight these.
 
Max thanks, but in trying this with a dummy spread sheet it appears that this happens
The first duplicate record of every duplicate record get selected and by deleting the "unique" rows I lose a record I want. Am I doing something wrong???? or is this the way your way works?????
 
Well, duplicates are afterall by "definition"
the 2nd, 3rd, 4th, etc instances of for example,
in this case, names occuring in the NAME column.

The method suggested will give you the list of duplicates.
(which was your objective and subject line?)

Uniques are the first instances of names in the column
(If you want to keep this uniques list as well,
do a copy > paste special > values on another sheet
before you delete the list)

Another way to "tag" duplicates of the names
using a helper column, assuming the names are
in col A, A2 downwards:

Put in B2:

=IF(COUNTIF($A$2:A2,A2)>1,"Name is duplicate of cell
"&ADDRESS(MATCH(A2,$A$2:A2,0)+1,COLUMN(A2),4),"")

copy down col B

Col B will identify duplicates found in col A, if any,
and indicate the cell ref for you to check / work on.

You could use Data>Filter>Autofilter on col B to filter out
systematically the duplicate rows for your checks.

--
hth
Max
-----------------------------------------
Please reply in thread

Use xdemechanik <at>yahoo<dot>com for email
----------------------------------------------------------------------------
Steve Masters said:
Max thanks, but in trying this with a dummy spread sheet it appears that this happens:
The first duplicate record of every duplicate record get selected and by
Click to expand...
deleting the "unique" rows I lose a record I want. Am I doing something
wrong???? or is this the way your way works?????
 
Back
Top