Need Execel to do math and spit out Data

First time I'm posting here, not sure if I'm in the right place.

I have a problem I think is basic but not sure how to solve it. I do mailing services at the company I work for and a certain type of mail service (EDDM), requires us to make forms called 'facing-slips'. When our software spits out reports there is a section that covers delivery routes. These are the actual walking routes that a postman will take while out delivering mail. Some are large routes, others not so much.

I have attached three files. The (Working) excel file has data in it that I would use to create the data to plug into the PDF sample that is also attached. The (Need) excel file is an example of what I need to have output by excel.

Finally, this is what needs to happen!
In the Excel file there are only three columns that are important, Zip | CRRT | Total Residential. If you look in row 4 you will see the CRRT is C012 and the Total Residential is 1,262. I'm only using this line in the example because it's a larger number. Each Route will get a sack of bundled mail pieces, in this case C012 would have a sack with 5 bundles. Each bundle needs to have a facing slip and in this case the max number of mail pieces is 250/+ but not greater than 300.

I need excel to divide 1,262 by 250 and determine that would be 4 bundles of 250 and 1 bundle of 262. Then I need it to create 5 rows in a spread sheet that have the: Zip | CRRT | Total # of Pieces Per Bundle | Bundle number | Total Bundles as the columns of information.
It would need to do this for every row in the excel file. I would then need to be able to create a PDF and import the data from Excel so I can have a single file that has all of the facing slips in the order of the CRRT sequence that is in the original working file.

Is this crazy to do out of Excel? Any ideas would be helpful. As of now all of these slips need to be filled in by hand and when doing a project with half a million pieces of mail it takes a couple days to complete.

Thank you in advance for any ideas or solutions!

P.S. I forgot to attache the files and now I'm not sure how to do it. If you read this and have ideas I can send you the files to review. Thanks again!

Hi and welcome to the forum.

You will need to add the files so that we can be sure what we're working with.
Edit your post, choose Go Advanced and underneath the post look for the Manage Attachments option and take it from there.

Hi ram-estimating,

I combine both excel to sample tab, working tab and formula tab(result).

First, it have two helper column in the working tab, in the front of data (column A, due to limitation of vlookup), and the last column (column I)

in A2, key in below formula and paste to last row+1, (ie. if have 10 row of data, copy to 11th row)

Formula:

=IF(ISNUMBER(A1),IF(ISBLANK(B2),A1+I1-1,A1+I1),1)

This is to determine the first row of each "CRRT" and the last row of "CRRT" needed.

in I2, key in below formula and paste to last row

Formula:

=IF(MOD(F2,250)<=50,ROUNDDOWN(F2/250,0),ROUNDUP(F2/250,0))

This is to find how many bandle need for each "CRRT"

Mod is find the remain amount after divide (ie, Mod(5,2) the result is 1)

helper column done, now the result part

in formula tab, have to add in one column in front.(column A)
in A2 to last row put down the series number (ie, 1,2,3,4,5....) till the last number in the Working tab column A (ie, this workbook is A22, value is 21)

in D2, key in below formula and paste to last row

Formula:

=VLOOKUP(A2,Working!$A$2:$C$7,3,1)

The false vlookup perform the repeat of "CRRT"

in C2, key in below formula and paste to last row

Formula:

=INDEX(Working!$B$2:$B$7,MATCH(Formula!D2,Working!$C$2:$C$7,0))

It find the Zip value in working tab based on column D

in E2, key in below formulaand paste to last row

Formula:

=IF((INDEX(Working!$F$2:$F$7,MATCH(D2,Working!$C$2:$C$7,0))-SUMIF(D$1:D1,D2,E$1:E1))>300,250,INDEX(Working!$F$2:$F$7,MATCH(D2,Working!$C$2:$C$7,0))-SUMIF(D$1:D1,D2,E$1:E1))

it check whether the remain value is exceed 300, if yes, 250, if not will show remain that less than 300.

in F2, key in below formula and paste to last row

Formula:

=COUNTIF(D$2:D2,D2)

It count the occurrence of "CRRT" from top to end

in G2, key in below Array formula and paste to last row

Formula:

=MAX(IF($D$2:$D$22=D2,$F$2:$F$22))

It find the max value in column F, by same "CRRT"


PS: array formula is need to confirm by Ctrl + Shift + Enter

If not understand, please follow below step:

1, Paste the formula to the cell
2, Double click on the cell
3, press Ctrl + Shift + Enter
It show { sign in front of formula. (ie "{=ind.....)

duplicate post

I have adapted the formulas from post #4 to the file attached to post #11. You'll need to see if the results look like what you would expect.
Let us know if you have any questions.

F2 and F3 are 1, at least on the copy of the file that I am looking at.
Could you tell us why G2 and G3 should be 1?

Take a look and see if this has things sorted out.
Let us know if you have any questions.

Hi,

sorry, the main reason is I did not know the name of CRRT have duplicate value.
I had edit abit to suite your case,
But JeteMc is have the easier way to do it, so I better further explain a bit on his work instead of mine.

He change the C3 with

Formula:

=INDEX(Working!$B$2:$B$205,MATCH(Formula!A2,Working!$A$2:$A$205,1))

formula that act similar to D2, find value based on the ref#

and E3 change to array formula,

Formula:

=IF((INDEX(Working!$F$2:$F$205,MATCH(1,(Working!$B$2:$B$205=C2)*(Working!$C$2:$C$205=D2),0))-SUMIFS(E$1:E1,C$1:C1,C2,D$1:D1,D2))>300,250,INDEX(Working!$F$2:$F$205,MATCH(1,(Working!$B$2:$B$205=C2)*(Working!$C$2:$C$205=D2),0))-SUMIFS(E$1:E1,C$1:C1,C2,D$1:D1,D2))

my one is only search the first value of CRRT, it not suitable to duplicate value.
JeteMac formula is compare to the Zip as well, the double match function will eliminate the duplication.

hope you understand how it works.