Macro for duplicate records

[savio21]

Hi Everyone,

I have a spreadsheet with over 60K entries and it grows on a monthly basis. I'm looking for a macro that will search a single column and return all entries that are duplicates. I have attached a workbook as an example to the data I will be viewing.

The macro will need to look under Column F and create a list (can be in the same sheet or separate sheet) of all the entries that have duplicate serial numbers.

Thanks in advance for your help.

[JBeaucaire]

You don't really need a macro, you can add a column to the data to do the "test" then apply a standard AutoFilter to see the resulting "duplicate rows" right on the original sheet.

[savio21]

The problem lies with the number of records, with over 60,000 records sifting through a list that identify the duplicates can become difficult. It is why I was hoping to have a macro run through the table, identify a duplicate, paste it in a new sheet or the side and then list the number of times it was repeated. Hope that clarifies what I'm trying to accomplish.

Thanks though for the effort.

[arlu1201]

Hey Savio,

You can try this code.

Please Login or Register  to view this content.

It will transfer all the duplicates to Sheet 2 and then give you a count of the duplicates per serial number in Sheet 3.

[watersev]

hi, savio21, please check attachment, run code "test"

[arlu1201]

Did the code work for you?

[savio21]

Unfortunately, it didnt work artu. I get a "Run time error '1004'. Select method of Range Class failed" and debugging highlights this part of the code:

Please Login or Register  to view this content.

Watersev, your code doesn't accomplish exactly what I'm looking for. I'm expecting the serial number to show up and then a column beside it to identify the number of times it is repeated. For example, the test file should report 2111 in a different cell or sheet and say its repeated 4 times (including the first occurrence).

[JBeaucaire]

Try this, then, it uses the formula suggestion I gave in post #2, but puts it in via macro, then filters the results for you onto another sheet, no looping.

Please Login or Register  to view this content.

Note, this will even work if you move your Serial # column.

[savio21]

Awesome, this is perfect! Thanks JBeaucaire!

[JBeaucaire]

One little edit to make it truly adjustable regarding the Serial # column being anywhere:

Please Login or Register  to view this content.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[savio21]

Unfortunately, I tried the code on my full data set and it took way too long to run (over 65,000 rows of data). Thanks for help though, it will certainly be useful when I'm using smaller data sets.

There doesn't seem to be an Edit box for my original post; I can see them for responses but not the original post.