Dividing up records into multiple spreadsheets based on pivot table counts

[cnzov]

I have a spreadsheet (around 200k rows) of addresses. The spreadsheet is sorted by ZIP code.

I need a way for excel to think "there are 5,436 records in the first zip code (let's call it 90210), user wants to break the records with zip code 90210 into 30 separate sheets, meaning the first 181 (rounded down) names in 90210 will go on SHEET 1, the second 181 names in 90210 will go on SHEET 2, and so on until SHEET 30 is reached. Sheet 30 would have the remaining names (considering rounding) so it would likely be larger.

Then, excel would need to move to the next ZIP code (let's call it 90211 - with 2,500 records) and do the same thing, adding those ZIP codes evenly to the sheets outlined above.

I know this is somewhat complicated however I've learned that close to everything is possible in excel - any ideas? Any help is appreciated!

[popipipo]

Why do you want to change 1 sheet with 200.000 records in 1100 sheets with each 180 records?

[newdoverman]

Is there anything "special" about the first 181 or could any 181 of the 5436 be chosen as long as there were 181 chosen for each worksheet?

The reason one would actually break up a database of zip codes escapes me. What is the purpose this is being put to?

[cnzov]

No - nothing special about the first 181 records. It can be 181 records at random within that first zip code.

The spreadsheet will have 200,000 lines (approximately). It will have 40 different zip codes. Each zip code will have a different number of records. We are creating mailing lists - the lists need to be broken up evenly so an even portion of each zip code is mailed throughout the year (used 30 weeks as an example).

[newdoverman]

Something "quick and dirty" though probably accurate enough is to insert a column and enter this formula next to the starting record and fill down:

Please Login or Register  to view this content.

This will result in a repeating range of values from 0 to 29 the full length of your data. You can then use these values to filter your data for a mail merge without having 30 worksheets. The results of a filter could also be copied to a new worksheet if necessary by selecting the range of the filtered records by going to the Home tab, Find and Select, Special, Visible Cells Only, OK. Click, Copy then on a new worksheet click paste and all the filtered records will be pasted onto the new worksheet. This will keep the size of your workbook to a minimum and is quick and easy to do. The records can even be filtered in Word if that is what you are using to produce the mail out.

I enclose a small sample that you can play with to get the idea.

[cnzov]

Wow - simple yet effective. Thank you! This works well!

[newdoverman]

Thank you for the feedback.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.