Need a list of all duplicates in spread.

[Bailey_Thomas]

Hi my name is Tommy,
I'm a fairly regular poster here and I've come across an issue I'm not quite sure how to solve.
I have a spread with a few columns of my products. I need to find all of the duplicates in one column... and somehow paste them into another column.
I know how to erase duplicates under the "Data" tab, but I need to know what I'm erasing so I can remove it from my website. This spread is over 30,000 rows so I can't do it all manually.
I'd appreciate any help or input, thank you in advance!

Happy Holidays!
-Tommy Bailey

[Glenn Kennedy]

Assuming your data with duplicates start in A1, put this in B2. Adjust the number of rows it is looking at to suit your needs (currently going down to row 100). it is an array formula andf must be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

then copy down until blank cells are seen

Please Login or Register  to view this content.

[Bailey_Thomas]

Maybe I'm misreading but I guess I'm not activating the formula properly. Maybe you can tell me why by looking at my spread.

[Sean Thomas]

or you can do it with vba.
this will copy all duplicates from sheet1 column A onto sheet 2 and if you have other data in column B it will copy that over as well.

[Glenn Kennedy]

You hadn't set the array formula properly. Neither had you adjusted the ranges. I am trying to do this now for you, but with 30,000 rows, it might take a time!. I'll keep the laptop on while i drive home. bit in the emantime you could try the VBA solution if you like.

[Bailey_Thomas]

I've tried the VBA solution as well... It works in small scale but I'm running into the same issue, I have so many rows that Excel is just ghosting out.
I really do appreciate the help, you are awesome!

[Glenn Kennedy]

My Pc crashed. I'll try on another machine later...

[Bailey_Thomas]

I've figured out a solution. For any viewers that are curious here's how I did it.

Please Login or Register  to view this content.

[Sean Thomas]

try changing
Dim nextrow As Integer
to
Dim nextrow as long

integer only goes to 32,767

[MarvinP]

Hi Tommy,

I think a very quick and simple Pivot Table will work for this problem. No formulas or VBA needed. After adding a column name of Product I did a Pivot Table and a count of each Product ID. Then using a Row Filter only showed the counts that were greater than one. This is the list I think you want. See the attached.

[Glenn Kennedy]

Well, for a bit of a laugh, I left the array formula running... overnight. It did work (or rather it was till working), when I put it out of its misery this morning. the Pivot Table approach will give you a unique list of duplicates and is (by far) the best. However - to prove a (small) point here was some of the work in progress, just before I killed it!!

So slow.jpg