How to find all combinations within a constraint

NLindley7

New Member
Joined
Sep 11, 2019
Messages
1
I am after some code/advice for Access.
I have a list of Order Numbers(Unique) and what cubic metres that each are, I also have a cost benefit per Order.
I would like to run some code to be able to find every combination of Orders but within a given constraint of Cubic size (65Cube) to find the most cost effect mix of Orders in the minimum amount of results (Lowest Container Loads)
So if I had for instance 9x PO's below, in this case due to them all being 20Cube, the minimum you could build would be 3 containers, but which combo would (not duplicating) output the highest cost benefit.
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">11111 20Cu £600
22222 20Cu £2,500
33333 20Cu £30,000
44444 20Cu £10,000
55555 20Cu £300
66666 20Cu £23,000
77777 20Cu £50,000
88888 20Cu £100
99999 20Cu £1,000
</code>There are 84x Combinations in this case and top three would be:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">333333 666666 777777
222222 444444 999999
111111 555555 888888
</code>In the real case, there shall be 1800+ PO's and all of different cube.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,508
I don't think you have defined the problem clearly (i.e., what is Lowest Container Load and how do you calculate cost effectiveness). Also your sample solution of the top 3 lists 9 POs, so its not a valid solution. There is really very little that makes sense in having one order provide 50,000 pounds of cost effectiveness for 20 cubic volume and another only 100 pounds of cost effectiveness for the same cubic volume - its as if you have one order for gold and another for iron ore - not a practical problem really (do you need iron or gold - you can't really substitute one for the other).

By the way this really has little to do with Access - generally its a problem you would tackle with Excel solver or some other linear programming model.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,128
Office Version
365
Platform
Windows
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

Forum statistics

Threads
1,077,664
Messages
5,335,570
Members
399,025
Latest member
alce

Some videos you may like

This Week's Hot Topics

Top