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,527
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,177
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,078,441
Messages
5,340,298
Members
399,364
Latest member
farhan11941234

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top