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.

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"djExcel" <(E-Mail Removed)> wrote in message
news:4CB19480-D002-4673-9D65-(E-Mail Removed)...
> 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.

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

"djExcel" wrote:

> 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.