Waybill Puzzle - Max weight, total lbs to max 150, carry over remainder & repeat

[SusanE1960]

I am fairly experienced with excel. I have discussed this problem with my co-excel nerd. We can't come up with a solution.

I ship monthly ten or so different items, in different quantities and weights. I am permitted to put 150 lbs on a waybill, then must start a new waybill.

My department does it manually. I want excel to sort it for me. I keep coming back to it and can't solve it.

[PeteABC123]

Have you worked with SOLVER? Sounds like your ticket.

Link provided through Excel Help. https://www.solver.com/excel-solver-online-help

Pete

[SusanE1960]

Never heard of solver. I will explore that further. Isn't excel just such a wonderful way to keep learning.
Thanks

[Alf]

I've made two possible setups for solver. As this is a simple problem I used the Simplex engine. As you problem is easy to define one does not need to specify a Max, min or a
specific value for the target cell. Just setting the constraints are sufficient.

Alf

[Hydraulics]

Starting from Alf's setup, here is a different one using integer variables. It can split multiple items among bins.

Please note there is no guarantee that the first bin will be filled as much as possible before starting the second one, and so on.

If this is what you need - minimize the number of bins - then we need to solve a multiple bin packing problem, and that is much harder.

There is a free Excel worksheet available here.

HTH,

Francesco

[Alf]

Hi Francesco!

Your idea of using integer instead of binary settings is a much better solution than mine! Still I could not resist fiddling a bit with it. As none of us know at the moment if the OP has
any preference how the solution should be I added an object cell C16=G16+I16 and as function is set to max G16 and H16 both will be 150, cell I16 takes the balance 48.5.

I also deleted some of your constraints as they were not needed in my setup.

If on the other hand OP wishes to distribute the total weight evenly between Waybill 1, 2 and 3 changing G18 and H18 to 116. This will give the result G16 and I16 = 116 and
balance of 116,5 goes to Waybill 3.

Alf

[astupig]

Use Weighted Sumproduct, we can fill the first waybill to 150 first.

see attached.

[kvsrinivasamurthy]

In G3 then copied across.

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Better option than my previous post. Pl see file
In G2 then copied across.

Please Login or Register  to view this content.