Create least possible amount of sum combinations up until a certain maximum out of number list

[vicente87]

Hi all,

I would like to reach out to your expertise for a very complex problem of which I'm not even sure whether it's solvable using Excel.

I work for a logistics company, and have this database of shipments containing a certain amount of pallets. The shipments are divided into groups that are allowed to be shipped in the same time. What I need to do now is find the least number of trucks to combine these shipments in within each time group (without splitting shipments in several trucks), knowing that the maximum number of pallets in a truck is 26.

Please see example of data below (the actual list is 18000 rows, so I can't perform this exercise manually). Input format in the first two columns. Third column is how the output would be like. I would need to have the number of pallets per truck formed.

Shipments</SPAN>	Pallets</SPAN>	Desired outcome</SPAN>
GROUP 1 total</SPAN>	34</SPAN>
destination 1 total</SPAN>	34</SPAN>
Shipment 1</SPAN>	6</SPAN>	26</SPAN>
Shipment 2</SPAN>	6</SPAN>	8</SPAN>
Shipment 3</SPAN>	6</SPAN>
Shipment 4</SPAN>	6</SPAN>
Shipment 5</SPAN>	6</SPAN>
Shipment 6</SPAN>	2</SPAN>
Shipment 7</SPAN>	2</SPAN>
GROUP 2 total</SPAN>	14</SPAN>
destination 2 total</SPAN>	14</SPAN>
Shipment 1</SPAN>	4</SPAN>	14</SPAN>
Shipment 2</SPAN>	4</SPAN>
Shipment 3</SPAN>	3</SPAN>
Shipment 4</SPAN>	3</SPAN>
GROUP 3 total</SPAN>	78</SPAN>
destination 3 total</SPAN>	78</SPAN>
Shipment 1</SPAN>	22</SPAN>	22</SPAN>
Shipment 2</SPAN>	21</SPAN>	21</SPAN>
Shipment 3</SPAN>	8</SPAN>	23</SPAN>
Shipment 4</SPAN>	6</SPAN>	12</SPAN>
Shipment 5</SPAN>	7</SPAN>
Shipment 6</SPAN>	8</SPAN>
Shipment 7</SPAN>	6</SPAN>
GROUP 4 total</SPAN>	35</SPAN>
destination 4 total</SPAN>	35</SPAN>
Shipment 1</SPAN>	20</SPAN>	20</SPAN>
Shipment 2</SPAN>	15</SPAN>	15</SPAN>
GROUP 5 total</SPAN>	44</SPAN>
destination 5 total</SPAN>	44</SPAN>
Shipment 1</SPAN>	5</SPAN>	26</SPAN>
Shipment 2</SPAN>	5</SPAN>	18</SPAN>
Shipment 3</SPAN>	4</SPAN>
Shipment 4</SPAN>	4</SPAN>
Shipment 5</SPAN>	3</SPAN>
Shipment 6</SPAN>	4</SPAN>
Shipment 7</SPAN>	3</SPAN>
Shipment 8</SPAN>	3</SPAN>
Shipment 9</SPAN>	5</SPAN>
Shipment 10</SPAN>	4</SPAN>
Shipment 11</SPAN>	4</SPAN>
GROUP 6 total</SPAN>	25</SPAN>
destination 6 total</SPAN>	25</SPAN>
Shipment 1</SPAN>	22</SPAN>	25</SPAN>
Shipment 2</SPAN>	3</SPAN>

<TBODY>
</TBODY>

Any help or even guidance in some direction would be most welcome. Thans a lot!

</SPAN>

<TBODY>
</TBODY>

 

[shg]

Wouldn't the first two values be 24 and 10 rather than 26 and 8?

 

[vicente87]

No, the purpose is that all trucks would be maximally loaded with 26 being the maximum number of pallets. 26 and 8 is therefore better than 24 and 10. Although both possible, the former would be the optimal solution here.

 

[shg]

I can't reconcile that with this:

What I need to do now is find the least number of trucks to combine these shipments in within each time group (without splitting shipments in several trucks)

 

[vicente87]

I can't reconcile that with this:

By "without splitting shipments" I mean that one shipment cannot be divided over two trucks. FE shipment 1 for destination 5 should completely be in truck 1 or truck 2. (Else the issue would be easy to solve)

 

[shg]

That's equivalent to cutting items efficiently from fixed-length stock. There's a workbook that will do this, one group at a time, at https://app.box.com/shared/uhrjy318l1

 

[vicente87]

Thanks a lot shg. This does seem very complex to me. How exactly would I apply this workbook to my example? Would it be able to help me knowing that I have over 1000 groups to cut?

 

[shg]

For your group 5, it generates this output:

	B​	C​	D​	E​	F​	G​
9​	Item​	Total Qty​	11​	Stock #​	1​	2​
10​	​	Length​	Qty​	Cut\Scrap​	0.0​	8.0​
11​	1​	5​	1​	1​	1​	​
12​	2​	5​	1​	1​	1​	​
13​	3​	4​	1​	1​	1​	​
14​	4​	4​	1​	1​	1​	​
15​	5​	3​	1​	1​	1​	​
16​	6​	4​	1​	1​	​	1​
17​	7​	3​	1​	1​	​	1​
18​	8​	3​	1​	1​	​	1​
19​	9​	5​	1​	1​	1​	​
20​	10​	4​	1​	1​	​	1​
21​	11​	4​	1​	1​	​	1​

That shows which shipments go on which truck.

The code could be modified to load to read the groups in from another sheet, and write the outputs back out, but it's not something I have time to do.

 

[vicente87]

Hi shg,

No of course I understand you don't have time to do this. You have already been of great help. Thanks a lot!

 

[shg]

You're welcome.