Spreadsheet: 727823d1618137530-building-a-truck-even-pallet-quantities-pallet-builder_mod1
Sheet: Example

Truck # | Item # | Lot # | Description |  | QTY (Cases) | Cases per Pallet | # of Pallets |  | Truck 1 | Truck 2 | Truck 3 | Truck 4 | Truck 5
1 | 4563 | 1065A | 24 ct Vanilla |  | 2760 | 120 | 23 |  | 0 | 0 | 0 | 0 | 0 |  | <= | 1
2 | 4563 | 1065A | 24 ct Vanilla |  | 1440 | 120 | 12 |  | 0 | 0 | 0 | 0 | 0
2 | 4563 | 1065P | 24 ct Vanilla |  | 1320 | 120 | 11 |  | 0 | 0 | 0 | 0 | 0
3 | 4563 | 1065P | 24 ct Vanilla |  | 2760 | 120 | 23 |  | 0 | 0 | 0 | 0 | 0 |  |  | The total sum of pallets are 289,7 dividing this number with the [NAME]
4 | 4563 | 1065P | 24 ct Vanilla |  | 2760 | 120 | 23 |  | 0 | 0 | 0 | 0 | 0 |  |  | number of pallets in a load i.e. 26 finds a value of 11,1 so a minimum of
5 | 4563 | 1065P | 24 ct Vanilla |  | 2760 | 120 | 23 |  | 0 | 0 | 0 | 0 | 1 |  |  | 12 loads are needed to handle all pallets.
6 | 4563 | 1065P | 24 ct Vanilla |  | 2760 | 120 | 23 |  | 0 | 0 | 0 | 0 | 0
7 | 8974 | 1068P | 24 ct Chocolate |  | 360 | 120 | 3 |  | 0 | 0 | 0 | 0 | 1 |  |  | In order to solve this problem using the excel solver I've broken it  down
7 | 8974 | 1068P | 24 ct Chocolate |  | 2400 | 120 | 20 |  | 0 | 0 | 0 | 0 | 0 |  |  | to two loads of five each and a last load of two.
8 | 8974 | 1068P | 24 ct Chocolate |  | 2760 | 120 | 23 |  | 0 | 0 | 0 | 0 | 0
9 | 8974 | 1068P | 24 ct Chocolate |  | 1200 | 120 | 10 |  | 0 | 0 | 1 | 0 | 0 |  |  | Clear range J5:N31 and run solver. As solver is set to run for 10 seconds
9 | 8974 | 1068P | 24 ct Chocolate |  | 1560 | 120 | 13 |  | 0 | 0 | 0 | 1 | 0 |  |  | the "Show Trial Solution" window will pop up tellin that the maximum time limit
10 | 8974 | 1068P | 24 ct Chocolate |  | 840 | 120 | 7 |  | 1 | 0 | 0 | 0 | 0 |  |  | was reached. Click "Stop" and "OK"
10 | 8974 | 1068P | 24 ct Chocolate |  | 1920 | 120 | 16 |  | 0 | 0 | 1 | 0 | 0
11 | 8974 | 1068P | 24 ct Chocolate |  | 1200 | 120 | 10 |  | 1 | 0 | 0 | 0 | 0 |  |  | To see the result for the first 5 truck loads I've set up an autofilter in
11 | 8974 | 1068P | 24 ct Chocolate |  | 1560 | 120 | 13 |  | 0 | 0 | 0 | 1 | 0 |  |  | range J4:N4. For first load filter J column using criteria 1.
12 | 8974 | 1068P | 24 ct Chocolate |  | 2400 | 120 | 20 |  | 0 | 1 | 0 | 0 | 0
12 | 8974 | 1068P | 24 ct Chocolate |  | 360 | 120 | 3 |  | 0 | 1 | 0 | 0 | 0 |  |  | Copy the filtered range and afterwards clear the filtered H column
13 | 4563 | 1065P | 24 ct Vanilla |  | 111 | 120 | 0.925 |  | 0 | 1 | 0 | 0 | 0 |  |  | to get rid of the used pallets.
13 | 4563 | 1065P | 24 ct Vanilla |  | 40 | 120 | 0.3333333333333333 |  | 1 | 0 | 0 | 0 | 0
13 | 8974 | 1068P | 24 ct Chocolate |  | 105 | 120 | 0.875 |  | 1 | 0 | 0 | 0 | 0 |  |  | Repeat this for the first 5 loads, don't forget to clear the filtered
13 | 8974 | 1068P | 25 ct Chocolate |  | 307 | 120 | 2.558333333333333 |  | 0 | 0 | 0 | 0 | 0 |  |  | H range to ensure that these pallets are only used once.
13 | 8974 | 1068P | 26 ct Chocolate |  | 47 | 120 | 0.3916666666666667 |  | 1 | 0 | 0 | 0 | 0
13 | 8974 | 1068P | 27 ct Chocolate |  | 31 | 120 | 0.2583333333333334 |  | 0 | 1 | 0 | 0 | 0 |  |  | For the next run of 5 new loads change the "Min pallets" value to 22.
13 | 8974 | 1068P | 28 ct Chocolate |  | 88 | 120 | 0.7333333333333333 |  | 0 | 1 | 0 | 0 | 0
13 | 8974 | 1068P | 29 ct Chocolate |  | 62 | 120 | 0.5166666666666667 |  | 0 | 1 | 0 | 0 | 0 |  |  | Run solver again and treat the new set of 5 result in the same way as the previous one.
13 | 8974 | 1068P | 30 ct Chocolate |  | 852 | 120 | 7.1 |  | 1 | 0 | 0 | 0 | 0
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | For the last two loads set "Min pallets" 20 for cells J36 and K36
 |  |  |  |  |  |  |  |  | <= | <= | <= | <= | <= |  |  | and set the min  on cells [NAME], M36 and N36 to 0
 |  |  |  |  |  |  |  | [NAME] pallets | 26 | 26 | 26 | 26 | 26
 |  |  |  |  |  |  |  |  | => | => | => | => | => |  |  | Run solver a third time and extract values from last two truck loads.
 |  |  |  |  |  |  |  | Min pallets | 24 | 24 | 24 | 24 | 24
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | On rows 45 and 47 I've compaired the result for both the excel solver
 |  |  |  |  |  |  | Object function |  |  |  |  |  |  |  |  | as well as the OpenSolver.
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | And on rows 52 and 54 I've also shown the distribution of truck loads.
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | I.e. loads in the range of 25 - 26 pallets, loads in range 23 - 24,9, loads in
Truck # | Item # | Lot  # | Description |  | QTY | Cases | Pallets # |  |  |  |  |  |  |  |  | range equal to or  less than 22,9
Truck 1 | 8974 | 1068P | 24 ct Chocolate |  | 840 | 120 | 7
Truck 1 | 8974 | 1068P | 24 ct Chocolate |  | 1200 | 120 | 10 |  |  |  | Sum pallets | Average | Truck 1 | Truck 2 | Truck 3 | Truck 4 | Truck 5 | Truck 6 | Truck 7 | Truck 8 | Truck 9 | Truck 10 | Truck 11 | Truck 12
Truck 1 | 4563 | 1065P | 24 ct Vanilla |  | 111 | 120 | 0.925 |  | Excel solver result |  |  |  | 25.875 | 25.25833333333333 | 26 | 26 | 26 | 23 | 23 | 23 | 25.55833333333333 | 23 | 23 | 20
Truck 1 | 4563 | 1065P | 24 ct Vanilla |  | 40 | 120 | 0.3333333333333333
Truck 1 | 8974 | 1068P | 29 ct Chocolate |  | 62 | 120 | 0.5166666666666667 |  | OpenSolver result |  |  |  | 23 | 24.01666666666667 | 23 | 23 | 23 | 26 | 23 | 24.44166666666667 | 23 | 25.23333333333333 | 26 | 26
Truck 1 | 8974 | 1068P | 30 ct Chocolate |  | 852 | 120 | 7.1
 |  |  |  |  | Number of pallets |  |  |  | Distribution in load |  | Values | Values | Values
 |  |  |  |  |  |  |  |  | Nummer of pallets |  | >= 25 | =>23  <24,9 | <=22,9
Truck 2 | 8974 | 1068P | 24 ct Chocolate |  | 2400 | 120 | 20
Truck 2 | 8974 | 1068P | 24 ct Chocolate |  | 360 | 120 | 3 |  | Excel solver result
Truck 2 | 8974 | 1068P | 24 ct Chocolate |  | 105 | 120 | 0.875
Truck 2 | 8974 | 1068P | 26 ct Chocolate |  | 47 | 120 | 0.3916666666666667 |  | OpenSolver result
Truck 2 | 8974 | 1068P | 27 ct Chocolate |  | 31 | 120 | 0.2583333333333334
Truck 2 | 8974 | 1068P | 28 ct Chocolate |  | 88 | 120 | 0.7333333333333333
 |  |  |  |  | Number of pallets
Truck 3 | 8974 | 1068P | 24 ct Chocolate |  | 1200 | 120 | 10
Truck 3 | 8974 | 1068P | 24 ct Chocolate |  | 1920 | 120 | 16
 |  |  |  |  | Number of pallets
Truck 4 | 8974 | 1068P | 24 ct Chocolate |  | 1560 | 120 | 13
Truck 4 | 8974 | 1068P | 24 ct Chocolate |  | 1560 | 120 | 13
 |  |  |  |  | Number of pallets
Truck 5 | 4563 | 1065A | 24 ct Vanilla |  | 2760 | 120 | 23
Truck 5 | 8974 | 1068P | 24 ct Chocolate |  | 360 | 120 | 3
 |  |  |  |  | Number of pallets
Truck 6 | 4563 | 1065P | 24 ct Vanilla |  | 2760 | 120 | 23
 |  |  |  |  | Number of pallets
Truck 7 | 4563 | 1065P | 24 ct Vanilla |  | 2760 | 120 | 23
 |  |  |  |  | Number of pallets
Truck 8 | 4563 | 1065P | 24 ct Vanilla |  | 2760 | 120 | 23
 |  |  |  |  | Number of pallets
Truck 9 | 4563 | 1065P | 24 ct Vanilla |  | 2760 | 120 | 23
Truck9 | 8974 | 1068P | 25 ct Chocolate |  | 307 | 120 | 2.558333333333333
 |  |  |  |  | Number of pallets
Truck 10 | 8974 | 1068P | 24 ct Chocolate |  | 2760 | 120 | 23
 |  |  |  |  | Number of pallets
Truck 11 | 4563 | 1065A | 24 ct Vanilla |  | 1440 | 120 | 12
Truck 11 | 4563 | 1065P | 24 ct Vanilla |  | 1320 | 120 | 11
 |  |  |  |  | Number of pallets
Truck 12 | 8974 | 1068P | 24 ct Chocolate |  | 2400 | 120 | 20
 |  |  |  |  | Number of pallets