Excel 2007 : removing rows after counting / subtotaling a data in a column

[grizzlytoolguy]

Is there an fast or easy way to remove rows where the count on a particular column or field is less than 3? For example, I have a customer ID that appears on multiple records. Where there are less than 3 records per customer ID, I want to delete those rows and the associated count. Can this be done automatiically? If so, how? Please help.

[DGagnon]

you could run a macro on the data, do you have a sample that i could use to develop the macro?

[grizzlytoolguy]

Here is sample data. Any help would be appreciated.

100384021 12/04/2010 02 - Lost ROGERS TAMPA FL
100384021 Count 1
100384033 06/24/2011 02 - Lost SMITH PLANTATION FL
100384033 Count 1
100384038 07/08/2010 02 - Lost WILLS BUNNELL FL
100384038 01/03/2011 02 - Lost WILLS BUNNELL FL
100384038 02/05/2011 02 - Lost WILLS BUNNELL FL
100384038 Count 3

[DGagnon]

can you please attach as a workbook?

[grizzlytoolguy]

I'm sorry, but I can't figure out how to attach the sample spread sheet. Can you point me in the right direction? Very grateful for your assistance.

[grizzlytoolguy]

Ok, I figured it out. I have attached the sample file you requested. Thank you.

[DGagnon]

Try this macro out

Please Login or Register  to view this content.

[grizzlytoolguy]

Thank you for your help and taking the time. I appreciate it. The highlighted row shows a problem when I try to run the macro.

Excel Marco Code.GIF

[DGagnon]

your book is probably in .xls format, try changing the 100000 to 65536

[grizzlytoolguy]

I have 264003 rows and the file is saved as xlsx. I also have 3 worksheets in the file. Could that be an issue? I am still getting the same error "Overflow"

[DGagnon]

i did not expect you to have greater than 100,000 rows, so i used that number, try using 1048576 this is the max rows in a .xlsx book.

[grizzlytoolguy]

Ok, I'll give it a try. Thank you.

[grizzlytoolguy]

Thank you for your help. It was great!

[DGagnon]

no problem, please come back to ExcelForum if you have any other questions in the future.