Meredith
i hope i understood and translated your model good to the settings in range A1:G5.
For example prodA 114 = 19*6, but the others ???
Then suppose you want to ship 100 units of every product.
The (probably) best solution is to load a truck as good as possible with a combination of skids.
I hoped that i could combine skids in % of the use of a truck, row 5,
so the product of number of skids (=C12:G12) * % (=C5:M5) = M12 had to be less then 100% and is the value to be maximazid.
Then you check how many such trucks can be filled in B12.
The number of products shipped with this combination and number of trucks is in H12:L12.
Now, the question is whether you do the next step manually or with a macro (i prefer the last)
You copy the found solution B12:M12 as a new row into the table below.
This results in a saldo of remaining products in C9:G9.
Now you have to run the solver and copy that line until all products are shipped.
in B6, the number of trucks was 5.6 and after execution the number is 6 (B14) and the content is in C16:G21.
Is everything still understandable ?
VBA Code:
Sub Solve_Meredith()
With Range("Tabel1[truck]").ListObject 'listobject for the solutions
If .ListRows.Count Then .DataBodyRange.Delete 'delete previous solutions
Do 'in a loop
ptr = ptr + 1 'increment number of loops
SolverSolve True 'run solver
MsgBox "1" 'just tempory a msgbox after very run to see the result,
bflag = (Range("My_Truck").Value < 100) 'check if number of trucks>100, then terminate
If bflag Then
Set c = Range("B12:M12") 'range with the values
With .ListRows.Add.Range.Range("A1") 'add a new listrow
.Value = "Combination " & ptr 'combination number in A
.Offset(, 1).Resize(, c.Columns.Count).Value = c.Value 'all the other values
End With
End If
Loop While bflag And ptr < 20 'determine if you have to search further
End With
End Sub
| opl.xlsb |
|---|
|
|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M |
|---|
| 1 | product | | A | B | C | D | E | | | | | | |
|---|
| 2 | # | | 114 | 108 | 54 | 78 | 152 | | | | | | |
|---|
| 3 | skids/truck | | 19 | 36 | 18 | 39 | 38 | | | | | | |
|---|
| 4 | products/skid | | 6 | 3 | 3 | 2 | 4 | | | | | | |
|---|
| 5 | truck/skid | | 5,26% | 2,78% | 5,56% | 2,56% | 2,63% | | | | | | |
|---|
| 6 | estimate | 5,6 | 0,9 | 0,9 | 1,9 | 1,3 | 0,7 | | | | | | |
|---|
| 7 | | | | | | | | | | | | | |
|---|
| 8 | My QTY | | 100 | 100 | 100 | 100 | 100 | | | | | | |
|---|
| 9 | saldo | | 0 | 0 | 0 | 0 | 0 | | | | | | |
|---|
| 10 | skids | | 0 | 0 | 0 | 0 | 0 | | | | | | |
|---|
| 11 | | # trucks | skids A | Skids B | Skids C | Skids D | Skids E | A | B | C | D | E | |
|---|
| 12 | My Truck | 1000000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0,0% |
|---|
| 13 | | | | | | | | | | | | | |
|---|
| 14 | Totaal | 6 | | | | | | 100 | 100 | 100 | 100 | 100 | |
|---|
| 15 | truck | trucks | skids A | Skids B | Skids C | Skids D | Skids E | A | B | C | D | E | % load |
|---|
| 16 | Combination 1 | 1 | 0 | 0 | 18 | 0 | 0 | 0 | 0 | 54 | 0 | 0 | 100,0% |
|---|
| 17 | Combination 2 | 1 | 2 | 0 | 16 | 0 | 0 | 12 | 0 | 46 | 0 | 0 | 99,4% |
|---|
| 18 | Combination 3 | 1 | 15 | 7 | 0 | 0 | 0 | 88 | 21 | 0 | 0 | 0 | 98,4% |
|---|
| 19 | Combination 4 | 1 | 0 | 27 | 0 | 0 | 9 | 0 | 79 | 0 | 0 | 36 | 98,7% |
|---|
| 20 | Combination 5 | 1 | 0 | 0 | 0 | 22 | 16 | 0 | 0 | 0 | 44 | 64 | 98,5% |
|---|
| 21 | Combination 6 | 1 | 0 | 0 | 0 | 28 | 0 | 0 | 0 | 0 | 56 | 0 | 71,8% |
|---|
|
|---|
