find and copy multiple unique rows in data

[Jockeyp]

Hi,

I have a data set with about 30000 rows in it. There is a unique ID number in a each row, but the number repeats over a series of rows like this;

166885726​

166885726​

166885726​

166885726​

166885726​

166885726

For the vast majority of the data set each unique number will exist on 6 rows. However for about 300 instances the unique number only has 5 rows. And in an even smaller amount the unique number sits on 4 rows. And of course, these are all mixed throughout the data (not easily at the end). I can run a pivot table to tell me they exist - but I want to extract them from the data into a new sheet.

What I want to do is search the entire dataset, find the non 6 row unique occurrences, and copy all 5 rows of that unique code into a new sheet. Then do this again for all other instances of the 5 rows. Same for the 4 row ones into another new sheet. I suspect that this will be a VBA solution, I can work with VBA but admit, I don't know how to get it started to be able to find the non 6 row instances to then go into a loop of finding, highlighting, copying, pasting and back to finding again.

To be clear, I need all of the rows copied of that unique code as each row contains data I need to keep.

Hopefully that makes sense!

If anyone can help with a VBA code to solve this I would greatly appreciate it.

Thanks in advance

Neil

 

[Jockeyp]

Ok, so I am an idiot. Focused too much on the complicated and forgot about the simple. A good old COUNTIF formula identified the 5 and 4 row unique ones. wIth a filter, they can be easily copied and pasted either by hand or by VBA.

Sorry all - admins, feel free to mark as solved.