+ Reply to Thread
Results 1 to 5 of 5

Finding all combinations that make a certain sum

  1. #1
    Registered User
    Join Date
    08-01-2020
    Location
    Minneapolis, MN
    MS-Off Ver
    Office 365
    Posts
    2

    Wink Finding all combinations that make a certain sum

    Hello! I am currently building my own patio set and have figure out what sizes of wood pieces I need, but now I am trying to figure out the most efficient way to cut the wood with minimal waste (and how many wood boards to buy). I tried using the Solver function, but it only gives me one possible combination. I used 95 as my desired size ( 95 inches for an 8 foot board so I have one extra inch). If you have any better ideas that would be great!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,510

    Re: Finding all combinations that make a certain sum

    I've set up a Solver model based on how I understand your problem. I assume you wish to make as many lengths of 95" with a minimum wastage.

    This solver model loops through all you lengths util all are used. A wastage of 0 means the lengths adds up to 95" and a wastage of 0,5" means lengths adds up to 95,5"

    To run the macro "SolverLoop" you must first set a reference to Solver. Click Visual Basic Icon, Tools-> References and tick box marked Solver.

    Alf
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,510

    Re: Finding all combinations that make a certain sum

    As the first model solves the problem one row at the time the result may be sub-optimal i.e. the first result will be the best but as solver processes the rows the choice to
    find the best solution gets more and more limited.

    The best solution is found by letting solver find a solution for all rows in one go. But this makes the problem to big for the excel solver. That's why I've set up a model using
    the "OpenSolver" a freebie build by the University of Auckland NZ.

    It seems I uploaded the wrong file i.e. one I've used earlier in solving a similar problem. Have replaced this with one based on the OP's values from his uploaded file. Have tighten the gap a bit to 95 - 97,5 and now it takes about 37 - 38 seconds for "OpenSolver" to find a solution.

    Comparing the two files in my post one can see the effect of sub-optimizing as the result in the macro driven file diverge from the optimal solution at the end when solvers choices becomes limited as opposed to the "OpenSolver" solution.

    Alf
    Attached Files Attached Files
    Last edited by Alf; 08-03-2020 at 04:04 PM.

  4. #4
    Registered User
    Join Date
    08-01-2020
    Location
    Minneapolis, MN
    MS-Off Ver
    Office 365
    Posts
    2

    Re: Finding all combinations that make a certain sum

    This is perfect thank you so much! This was exactly what I was looking to do and the results are awesome. I ended up changing the size of the board since I realized the 10 foot boards were cheaper and this worked out perfectly to figure out how many to buy with minimum waste thanks so much for your help!

  5. #5
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,510

    Re: Finding all combinations that make a certain sum

    You are welcome and thanks for feed back.

    Alf

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Finding all combinations in a cells
    By FrancisM2411 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-21-2018, 06:09 AM
  2. Code for finding combinations
    By roadapples in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-10-2017, 03:49 PM
  3. Help in finding combinations
    By Sravanthi8 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-23-2015, 07:47 AM
  4. Function for finding SUM combinations
    By Kasper222 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-14-2014, 11:32 AM
  5. VBA - Finding unique combinations
    By xlsnovice in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-30-2013, 10:32 AM
  6. Finding possible combinations & listing the wanted combinations
    By Zoke in forum Excel Programming / VBA / Macros
    Replies: 30
    Last Post: 07-16-2012, 03:41 PM
  7. Finding combinations that add up to X
    By mj6987 in forum Excel General
    Replies: 4
    Last Post: 04-07-2006, 07:20 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1