Duplicate Values

  • Thread starter Thread starter SharonInGa
  • Start date Start date
S

SharonInGa

Is there a function that can locate duplicate values in a
column? I am currently sorting and manually looking
through the column for duplicates.
 
-----Original Message-----
Is there a function that can locate duplicate values in a
column? I am currently sorting and manually looking
through the column for duplicates.



.
Click to expand...
not sure if it is the best method but....
you could try putting a formula in the next column (assume
yr data is in A1:A100) put the following formula in B1
and copy down
=COUNTIF(A$1:$A8,$A8)
copy this formula into all cells B1 to B100. The formula
result will be '1' if not a duplicate. If it is
duplicated it will be '2' or above. Sort it by column B
to get all the '1' values to the top then delete all the
rows that have 2 or greater.
 
Try conditional formatting to highlight the cells that are
duplicates. This really makes them stand out for easy
identification:

Assume the values are in col A1:A1000

Select the range A1:A1000
Goto Format>ConditionalFormatting
Formula is: =COUNTIF(A$1:A$1000,A1)>1
Select maybe a yellow background fill color.
OK out.

Biff
 
Back
Top