D
DC
I have over 4,500 serial numbers on an Excel 2002 spreadsheet. How do I
quickly check/search for duplicate entries?
quickly check/search for duplicate entries?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
F
Frank Kabel
K
Ken Wright
Do you want to search for duplicates or just get a list of Unique IDs. If the
latter then just use Data / Filter / Advanced Filter, Tick copy to another
location and tick unique IDs only, select another cell to dump them and hit OK.
latter then just use Data / Filter / Advanced Filter, Tick copy to another
location and tick unique IDs only, select another cell to dump them and hit OK.
B
Bill MOrgan
DC,
You can sort the serial numbers in ascending order. This
will ensure that duplicates appear in consecutive rows.
Then go to the first empty column to the right of your
data and type in a conditional statement that identifies
the duplicates - Example: If(a1 = b1,1,0). Copy this
formula down to the end of your data. Now you have a "1"
next to each record that contains a duplicate serial
number.
Convert this new column to values (select the entire
column, then Copy / Paste Special / Values over to the
next column. Now you can sort the records by the new
column you created (descending order), and all the
duplicates are at the top of your sheet.
If you delete all the duplicates (the rows with a "1" in
the new column), you will be left with only one of each of
those serial numbers in your spreadsheet.
b.
You can sort the serial numbers in ascending order. This
will ensure that duplicates appear in consecutive rows.
Then go to the first empty column to the right of your
data and type in a conditional statement that identifies
the duplicates - Example: If(a1 = b1,1,0). Copy this
formula down to the end of your data. Now you have a "1"
next to each record that contains a duplicate serial
number.
Convert this new column to values (select the entire
column, then Copy / Paste Special / Values over to the
next column. Now you can sort the records by the new
column you created (descending order), and all the
duplicates are at the top of your sheet.
If you delete all the duplicates (the rows with a "1" in
the new column), you will be left with only one of each of
those serial numbers in your spreadsheet.
b.
S
SidBord
To do it quickly, you'll probably have to write a macro.
There are skillful folks out there who can work wonders
with the COUNTIF function and/or the SUMOFPRODUCTS function.
There are skillful folks out there who can work wonders
with the COUNTIF function and/or the SUMOFPRODUCTS function.
M
Myrna Larson
Assuming there is data in addition to the serial numbers, one way is to select
just the column containing the S/Ns, then use the Advanced Filter and check
the Unique Records box. You probably want to Filter In Place. Then you can
manually copy the visible rows to another location.
Another non-VBA method is to use a "helper" column with a formula like
=IF(COUNTIF($A$1:$A1,$A1)>1,"Duplicate","")
and copy it down. Then AutoFilter on this column to select rows showing
"Duplicate" and delete them. This approach will keep the first occurrence and
delete the others.
just the column containing the S/Ns, then use the Advanced Filter and check
the Unique Records box. You probably want to Filter In Place. Then you can
manually copy the visible rows to another location.
Another non-VBA method is to use a "helper" column with a formula like
=IF(COUNTIF($A$1:$A1,$A1)>1,"Duplicate","")
and copy it down. Then AutoFilter on this column to select rows showing
"Duplicate" and delete them. This approach will keep the first occurrence and
delete the others.