Welcome to the Board!
This is a variation of the Knapsack Problem. You can Google that, or check Wikipedia, but the upshot is that it's really a tricky problem. There are algorithms to do this, but it'd take more time than I have to adapt one of them to your particular problem. Excel has a tool called Solver that might work for you however. It's not installed by default though. If you don't have it on your Data tab, click File > Options > Add-ins > Go (on the bottom next to Excel Add-ins) > check the Solver box.
Now set up a new spreadsheet and set it up like this:
| A | B | C | D | E | F | G | H | I | J |
|---|
| 1 | Order | Volume | | Shipment | | 1 | 2 | 3 | 4 | |
| 2 | 1 | 145 | | | | 0 | 0 | 0 | 0 | |
| 3 | 2 | 135 | | | | | | | | |
| 4 | 3 | 846 | | | | Std. Dev. | | | | |
| 5 | 4 | 837 | | | | 0 | | | | |
| 6 | 5 | 349 | | | | | | | | |
| 7 | 6 | 161 | | | | | | | | |
| 8 | 7 | 549 | | | | | | | | |
| 9 | 8 | 1595 | | | | | | | | |
| 10 | 9 | 1595 | | | | | | | | |
| 11 | 10 | 1908 | | | | | | | | |
| 12 | 11 | 1895 | | | | | | | | |
| 13 | 12 | 1991 | | | | | | | | |
| 14 | | | | | | | | | | |
| 15 | | Max Per shipment | 3400 | | | | | | | |
| 16 | | Minimum Orders | 3.531176 | | | | | | | |
<tbody>
</tbody>
Sheet2
Worksheet Formulas
| Cell | Formula |
|---|
| F2 | =SUMIF($D$2:$D$13,F1,$B$2:$B$13) |
|---|
| G2 | =SUMIF($D$2:$D$13,G1,$B$2:$B$13) |
|---|
| H2 | =SUMIF($D$2:$D$13,H1,$B$2:$B$13) |
|---|
| I2 | =SUMIF($D$2:$D$13,I1,$B$2:$B$13) |
|---|
| F5: =STDEV.P(F2:I2) | | | C16 | =SUM(B2:B13)/C15 |
|---|
<tbody>
</tbody> |
<tbody>
</tbody>
The formula in C16 will tell you the minimum number of shipments you'll need (rounded up). So in F1:I1 put the numbers 1 to 4. Then put the formula in F5.
Now from the Data tab, click Solver (far right). Set up the parameters like this:
Set Objective: F5
To: Min
By Changing Variable Cells: D2:D13
Subject to the Constraints:
D2:D13<=
4
D2:D13 = integer
D2:D13 >=1
F2:I2<=C15
Solving Method: Evolutionary
and click Solve. It'll ponder for a while (you can see the stats in the status bar) and hopefully come up with a solution. If it doesn't, and it may not, don't give up hope yet. The algorithm is very dependent on starting conditions. Change D2:D13 all to 4 and try again. The numbers in column D will be the shipment that the order on that line should go in. If you still don't get a good solution after a few tries, there may not be a solution, or this algorithm might not find it. Then you might have to assume that you need 5 shipments. You'd just add a 5 in J1, the formula in J2, and update the formula in F5. Then try Solver again, changing the constraints I marked in red.
Incidentally, I chose Std. Dev. as the formula since that will have the effect of balancing the shipment sizes as much as possible. Other options are possible.
Hope this works for you!
