# Container assignment with constraint.

##### New Member
Dear Friends,

I would like to get your suggestions to solve the following problem by using M Code or power query.

How to pack the items of Table 1 in minimum number of carton subject to

1. Total weight in a carton should be max of 25 kgs.

2. In a caton max 5 items can be inluded.

3.The final table to be sorted from smallest to largest based on Pack No.

Thanks

JP
This problem already posted in myonlinetraininghub forum also.

https://www.myonlinetraininghub.com/excel-forum/power-query/conversion-of-table-by-using-power-query

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

MOD EDIT: Thread moved from PQ forum as there is no appropriate solution to the problem using PQ. Thread title changed to more closely match issue at hand.

#### Attachments

• 11.5 KB Views: 4
Last edited by a moderator:

#### Chihiro

##### Excel Ninja
This sort of thing is best done using VBA or using multi-stage set up in Excel.

PowerQuery isn't really the right tool for it.

But looking at your data... I can't see logic used in getting from Data Table to Result.

How come CD is changed from 12 KG to 10 KG and not AB from 15 KG to 13 KG?

Why is OP increased from 5 KG to 12 KG? etc.

You'll need to explain step by step how the result is generated from Data Table (manually).

##### New Member
Dear Chihiro,
Yes, change in the weight for CD & OP is a sorting mistake.
The corrected file is attached herewith and the manual step for the solution is also shown in the file.
I am working on a small project to pack the items, hence the solution required. The number of items may change from 1 no to max 100 nos and the packing solution required with automation. I already made a solution with multistep by using formula and I thought that Power query can give a simple solution & easy to automate.
If you feel the VBA is the right solution, I request you to give suitable vba code for the above solution. Somebody told me that, by using solver, we can get a easy solution.

Thanks

JP

#### Attachments

• 12.6 KB Views: 1

#### Chihiro

##### Excel Ninja
Ok, looking at your updated sample. This looks to be job for Solver, using Simplex Linear model.

However, you'd still need intermediate step (stage). You'll need to create matrix of item and container #.

After the code is run, you'd sort table using Sort Index.

When number of variables (container product matrix) becomes too large (200+ or so), you may hit limit of Solver.

You may want to install OpenSolver add-in. Which removes constraint on number of variables. Developed by University of Auckland found in link below (currently lead by Jack Dunn from MIT).
https://opensolver.org/

See attached sample for set up. It may not give exact result. But would give close. You can tweak set up by adding more constraint etc to see if you can replicate some of your logic.

Notes:
- I didn't make this set up very dynamic, but this should be enough for you to change and modify set up to suite your need.
- Reference to Solver is added to project in VBE.

#### Attachments

• 18.3 KB Views: 1

##### New Member
Dear Chihiro,

Thanks for your suggestions and solutions by using solver.
I tried to add more columns for containers & rows for items and it is not working perfectly.
I think dynamic solution may not be possible.
Let us see anybody is coming with a different solution for this.
You may suggest improvements if any.
Once again thanks for your effort.

JP

#### Chihiro

##### Excel Ninja
I think dynamic solution may not be possible.
It's certainly possible. I just don't have the time at the moment to do full set up for you.

##### New Member
Dear Chhiro,

Whenever you are time, please try for the dynamic solution, which will be highly appreciable.

Jp

#### Chihiro

##### Excel Ninja
As I suspected, you'll hit too many variables in your matrix.

You will need to install OpenSolver and use that, instead of standard solver.

Have a read of their documentation etc.

See sample attached with Model shown. I used following set up.

#### Attachments

• 23.6 KB Views: 6