Postal Paper Work

I work for a printing company which does an enormous amount of mailing. Unfortunately we have to supply all this paperwork which is generated from the US Postal System web site which is NOT very user friendly and we have to manually calculate the number of slips we need. I have figured out how to copy the numbers off the site which would look like the example below .
Zip Route # of Mail Pcs
79404 CO01 682
79404 CO02 302
79410 CO01 192
79423 CO02 803

I have to print provide facing slips for each route, but I can't put over 300 cards in a bundle so in the above scenario I have to have a slips for each bundle which would say:
Zip Route Number Sequence
79404 CO01 300 pcs 1 of 3 bundles
79404 CO01 300 pcs 2 of 3 bundles
79404 CO01 82 pcs 3 of 3 bundles
Then the next route
79404 CO01 300 pcs 1 of 2 bundles
79404 CO01 2 pcs 2 of 2 bundles
Then the next route
79410 CO01 192 pcs 1 of 1 bundle

I'm having a total mental block on how to accomplish this task. I'm working on a mailing right now with 35 different routes and 20,102 post cards each route has a different number of cards and each route will require at least one facing slip and up to 4 slips with different information on each slip. Any help would be greatly appreciated.

Thanks
Randy

I don't the format of the facing slips but the attached creates the basic details (output on Sheet2).

Please Login or Register  to view this content.

I just typed a lengthy reply to you explaining the solution in detail, then when I clicked on Go Advanced to attach the file the Firewall intervened and I have lost everything that I typed. I'll get a coffee and then try again later.

Pete

Okay, I'll try again. The attached file shows how you can do this with a few formulae (as you had posted in the Formulas Forum). I've used 3 helper columns (shown in blue), which can be hidden once you have copied them down far enough to suit your data, and the expanded data that you want is shown in red. I've put your data in columns A to C, with this formula in D2:

=CEILING(C2,300)/300

which calculates the number of bundles you need for each route by rounding up to the next increment of 300 and then dividing by 300. The formula in E2 just gives a cumulative sum of these, i.e.:

=D2+E1

It is important that cell E1 contains zero (shown in yellow), and these two formulae should be copied down to the bottom of your data.

I've used this formula in G2:

=IF(ROWS($1:1)>MAX(E:E),"",MATCH(ROWS($1:1)-1,E:E)+1)

which expands your data and shows the row number where each bundle should get its data from. This formula (and the others that follow) should be copied down until you get blanks (I've copied to row 19 in the example file).

This formula in I2:

=IF($G2="","",INDEX(A:A,$G2))

will bring across the appropriate zip code from column A on the row given by column G. The formula can be copied into J2 to retrieve the route. The formula in J2:

=IF($G2="","",IF(COUNTIFS(J$2:J2,J2,G$2:G2,G2) < INDEX(D:D,$G2),300,MOD(INDEX(C:C,$G2),300)))

will give you the number of leaflets in each batch - either 300 or the remainder after dividing by 300. The final formula will give you the subtotal and grand total of bundles for each route, i.e.:

=IF($G2="","",COUNTIFS(J$2:J2,J2,G$2:G2,G2)&" of "&COUNTIFS(G:G,G2)&" bundles")

Hope this helps.

Pete

First of all I want to apologize to the respondents for not being honest to you. I said I was having a mental block and couldn't grasp how to do this. That was a lie because after looking at the spreadsheets I've been provided, I never had the mental capacity to build any of these sheets. I've pull a couple of databases and used them in these sheets and they work perfectly. Thanks so much for your help! I just submitted paper work for a 20,102 pc mailer which normally would take at least 1 uninterrupted hour to complete and did it less than 7 minutes. Just made my life EASIER!!!
Thanks again.

You're welcome - glad to be able to help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete