Check duplicates

[Guest]

What is the fastest way to check and mark duplicates in a long list. I have a
sheet, which contains about 12.000 user names and I need to set a text
'duplicate' in column B (user name is in column A). If I use two Do..Loop, it
takes like ages. Any ideas?

Code I've been using:

X = 1 'first row
Do Until Cells(X + 1, 1) = ""
Y = X + 1
Do Until Cells(Y, 1) = ""
If Cells(Y, 1) = Cells(X, 1) Then
Cells(Y, 2) = "duplicate"
Cells(X, 2) = "duplicate"
End If
Y = Y + 1
Loop
X = X + 1
Loop

Note: Same user may appear several times in the list.

 

[Bernard Liengme]

Have a look at
http://www.cpearson.com/excel/Duplicates.aspx
If you must, then most of Chips' idea could be implemented in VBA
best wishes

 

[Guest]

Without a macro, put this in b1 and double click the fill handle to fill the
formula down:-

=IF(COUNTIF($A$1:$A$1000,A1)>1,"Duplicate","")

Mike