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.
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.
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.
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.
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.
