Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check for duplicates? | Excel Worksheet Functions | |||
check for duplicates | Excel Worksheet Functions | |||
Check for Duplicates | Excel Worksheet Functions | |||
Check for Duplicates | Excel Worksheet Functions | |||
Check for Duplicates (RP) | Excel Programming |