locating duplicates
using Excel 2003
I have a list of invoice numbers and need to find duplicates. I tried using
conditional formatting in column E as such:
condition 1
formula is =COUNTIF(E:E,E1)1
(cell shading to yellow)
The problem I'm running into is that, for instance, it sees invoice numbers
0000003730 and 03730 as duplicates. I only want it to shade if it's an exact
match, i.e. 03730 and 03730.
I tried using a helper column (F) and in F2 put the formula
=IF(E2=E1,"duplicate","")
But I want ALL cells containing the same number to say "duplicate", i.e. all
occurences of 03730 should state duplicate, not just the second (or third or
fourth!) time it appears.
|