Consulting

Results 1 to 4 of 4

Thread: Macro to find best way to combine numbers to equal a certain sum

  1. #1
    VBAX Newbie
    Joined
    Oct 2020
    Posts
    1
    Location

    Macro to find best way to combine numbers to equal a certain sum

    Im pretty good at VBA, but this one is going to be a challenge I think. It'll be interesting for sure. I will have multiple rows and columns of numbers.. Something like this:
    7.4 , 7.4 , 7.4 , 7.4 , 7.4 , 7.4
    3.9 , 3.9 , 3.9 , 3.9 , 3.9
    11.9 , 11.9 , 11.9 , 11.9 , 11.9


    It will always be the same number in each column. You can assume it starts an A1. I will adjust as needed. But it could be any number of columns or rows.
    I want to enter a number in a cell or be asked by an input box or whatever... (lets say the number 12 but it could be anything up to 40) and have a macro find the best combinations of those numbers to come as close to 12 as possible without going over. It can use any of those numbers in any combination or order or as many as needed to come up with as few of combinations as possible.
    So in the example above, 1 possibility would be 11 combinations as follows: (best I can figure in my head quickly lol)
    Pair 7.4 with 3.9 five times (sum of 11.3 each pair)
    11.9 by itself 5 times
    Then one 7.4 left over to be by itself.

    Please note that numbers in the same row can be put together.. for example 3.9 could be put together 3 times to equal 11.7 but that's not the best way in this case because it would take 12 combinations to complete instead of 11. So the code can look at all these numbers as not being in any order or arrangement whatsoever.

    In case it matters, I want to buy as few 12 foot pieces of steel as possible to cut it and get each of the lengths listed the same number of times as the number of columns it's in. (7.4 feet six times, 3.9 feet five times and 11.9 five times)

    Unless Excel has some other way of doing this that I don't know about, it's going to be a challenging macro.

    Thank you!
    Albert

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Did you ever use a search method in this forum or any other Excel Forum ?

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by snb View Post
    Did you ever use a search method in this forum or any other Excel Forum ?
    What fun would that be?
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    A Cut list
    There are entire DLLs devoted to Cut Lists. They ain't simple.
    Most human minds do cut lists damn near as well as the best encoded program.

    Was a Professional Builder for 20 years. Still do building in my garage. Still do cut lists by hand.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

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