Filling diferent sized boxes

[scolty1985]

Hi,

I have a problem at work which I'm trying to solve and was hoping someone could give me some pointers. I have simplified the problem below:

I have varying sizes of pipe and varying sizes of baskets which I need to fill. I would like to be able to step through all of my baskets and check how many of the products I can fit in them before I need to start using the next basket, or until I run out of baskets.

IE I have 2 baskets and 4 products, if product 1 and half of product 2 fits in box 1, then I would fill the remainder of product 2 into box 2 and then put product 3 and 4 in box 2 assuming they fit.

I originally though about having a for loop for the baskets and a nest for loop for the products but I'm not sure this will work.

Any idea?

Thanks for your help.

 

[bhos123]

[TABLE="width: 717"]
<tbody>[TR]
[TD]Box Number[/TD]
[TD]Box Size[/TD]
[TD]Product Number[/TD]
[TD]Product Size[/TD]
[TD]Cumulative Box[/TD]
[TD]Cumulative Product[/TD]
[TD]Balance at each stage[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]70[/TD]
[TD]SUM($B$2:B2)[/TD]
[TD]SUM($D$2:D2)[/TD]
[TD]E2-F2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]225[/TD]
[TD="align: right"]210[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]275[/TD]
[TD="align: right"]270[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]325[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]
Enter the data in first 4 columns...and drag the formulas in last three columns, you can see the balance in each stage

 

[scolty1985]

Its a little more complicated. I have a weight (Te) and a volume (m3) check to do. So a product can fit in the baskets but exceed the weight therefore I cant load all of it in the basket and cant put any more in the basket.