Thread: finding duplicates in a worksheet

I have about 30,000 names in a worksheet and want to be able to search for duplicates. In the past, I have sorted my columns and done a simple IF formula comparing the cell prior. This works well trying to copy the formula down to all 30,000 cells is a little tedious.

Thanks,
seemore

Hi,

You don't need to sort your data. You could use an =COUNTIF($A1:$A30000,A1) and copy that down. Anything that returns > 1 is a duplicate. I don't see how you can avoid the copy down. You could write a simple Macro to do it. Assuming the =COUNT is in C1 just use

Range("C1").Copy Destination:=Range("C2:C30000")

If all you want to do is establish the unique items then you could use a Data Filter Advanced to extract a unique list to a blank column.

HTH

You can mark the double items in column A:

Sub tst()
  Range(Join(Filter([transpose(if(countif(offset($A$1,,,row(A1:A30000)),A1:A30000)>1,address(row(A1:A30000),1),""))], "$"), ",")).Interior.ColorIndex = 14
End Sub