Check duplicates
 

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.

Check duplicates
 

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" wrote in message
...
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.

Check duplicates
 

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.