Finding duplicate value

[Ayo]

Does anyone know of, or as any idea, a code that find duplicate values in a
range and then tells you the rows, or columns, where those duplicates are?

Thanks

 

[Sheeloo]

If your values with potential duplicates are in Col A then enter this in Col B
=Sumproduct(--(A1:A$1000=A1))
and copy down

If any cell has value greater than one it means that there is at least one
more value which is same in the column from that point onwards...

So if you have three "ABC" in Col A, you will get 3 against the first one, 2
against the second and 1 against the third.

You can keep the rows with 1 and delete all others to get uniqe values.

 

[ShaneDevenshire]

Hi,

2007 - one thing you could do is choose Home, Conditional Formatting,
Highlight Cells Rules, Duplicate Values.

2003 - select the range and choose Format, Conditional Formatting, pick
Formula is from the first drop down, enter the following formula in the
second box
=COUNTIF($A$1:$A$100,A1)>1
click the Format button and on the Patterns tab pick a color.

Both of these techniques provide a visual indicator of your duplicates. The
down side of a formula is that one formula can return only one result that
you can see, so to find all duplicates and indicate their location is
problematic.

If this helps, please click the Yes button