script or format to identify duplicate data

J

Jeremy

Is there a way I can write a script or conditional format to identify
duplicate data. The data can be marked by highlight if need be.

Ex
Since A1 and A3 are both the same numbers both locations become highlighted.
I have a large range of data to perform this in. The numbers being
identified are lager than 2 digits.
A
1 12
2 13
3 12


Thank you
 
L

Luke M

Conditional format would work fine:
With cell A1:
Formula is,
=COUNTIF(A:A,A1)>1
Note that if you put this formula in the column next to your data, you could
copy it down and then AutoFilter based on TRUE and whatever value is in
column A to find all the duplicates.
 
B

Bernard Liengme

Conditional Formatting
a) all version of Excel;
Select the column of numbers (I will assume this is column A) and open the
Conditional Formatting dialog. The formula you want to use is
=COUNTIF(A:A,A1)>1 and give a suitable format (light red fill, for example)
b) in Excel 2007: select the column of data, open the Conditional Formatting
dialog and locate "Format unique/duplicate..." and proceed from there.
best wishes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top