• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Container assignment with constraint.

Jayapresad

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

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).
 

Jayapresad

New Member
Dear Chihiro,
Thanks for your reply.
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

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 #.
69157

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

Jayapresad

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
 

Jayapresad

New Member
Dear Chhiro,

Thanks for your quick reply.
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.
69205
 

Attachments

Jayapresad

New Member
Dear Chihiro,

Thanks for your support.
Let me install open solver and try your suggestions.

I will come back to you.

JP
 
Top