Results 1 to 5 of 5

Thread: How to find all combinations within a constraint
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to find all combinations within a constraint

    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.
    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
    There are 84x Combinations in this case and top three would be:
    333333 666666 777777
    222222 444444 999999
    111111 555555 888888
    In the real case, there shall be 1800+ PO's and all of different cube.

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,470
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to find all combinations within a constraint

    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.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,633
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: How to find all combinations within a constraint

    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.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  4. #4
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,698
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to find all combinations within a constraint


  5. #5
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,731
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: How to find all combinations within a constraint

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •