# How to find all combinations within a constraint

#### NLindley7

##### New Member
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.

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### xenou

##### MrExcel MVP, Moderator
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.

#### Eric W

##### MrExcel MVP
What you're describing is a perfect example of the Knapsack problem.

https://en.wikipedia.org/wiki/Knapsack_problem

There is an algorithm described in that article, which could be easily adapted to Excel VBA. But as xenou said, it doesn't have much to do with Access.

#### Joe4

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.

1,102,872
Messages
5,489,428
Members
407,687
Latest member
NeoSez

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...