+ Reply to Thread
Results 1 to 39 of 39

How to make the combination of numbers and total of that combination should be in range?

  1. #1
    Registered User
    Join Date
    05-23-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    57

    How to make the combination of numbers and total of that combination should be in range?

    For example, i have 10 values say 25,87,95,100,105,110,125,130,200,250. I will set minimum target as 150 and maximum as 300. By running solver it should gave all possible combination without repetition within that range. Please help!

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: How to make the combination of numbers and total of that combination should be in rang

    I don't think Solver is a good tool to find ALL possible combinations.

    I'd rather use a macro for it. See such recursive code and sample file:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    05-23-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    57

    Re: How to make the combination of numbers and total of that combination should be in rang

    Thank you Kaper for your response.

    I checked the attached sample file. But i will require the target box i.e. minimum target & maximum Target. All combinations should be found within this targets only.

    Any other macro or sample file for this?

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Question Re: How to make the combination of numbers and total of that combination should be in rang


    It's weird to ask for help without an explanation at the level of any forum expects in the initial post
    neither with an attachment accordingly as we do not have to guess anything
    like where are stored your values, the targets, the result, …
    So that means you are so confident with your Excel / VBA skills to amend any code you may receive ?!

  5. #5
    Registered User
    Join Date
    05-23-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    57

    Re: How to make the combination of numbers and total of that combination should be in rang

    Dear Marc L,

    I tried to attach sample sheet for reference but unable to do.
    Basically i am working on a project where multiple sizes are being use to cut raw material to get the finished product. So by combining all those sizes, ultimate output (Size of Finish Product) should match. Yes, there is a tolerance given by say 3 cm. So if the Minimum Target is 368 cm then the maximum will be 370 cm. You can go through this attached jpeg file. Attachment 682018

    Attaching here with the sample excel file.

    Sample.xlsx
    Last edited by Darshan Shah; 06-11-2020 at 08:42 AM.

  6. #6
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: How to make the combination of numbers and total of that combination should be in rang

    You should have no problem adapting Kaper's macro to suit your needs.

    afind_value.jpg

    Alf

  7. #7
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Question Re: How to make the combination of numbers and total of that combination should be in rang


    Darshan,

    in your attachment columns H & I have 2 values, J & K have 3 values
    so any range for minimum & maximum number of values in a sum combination ?

  8. #8
    Registered User
    Join Date
    05-23-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    57

    Re: How to make the combination of numbers and total of that combination should be in rang

    Yes Marc. The only thing we have to do is the total of that combination should be within Minimum Width & Maximum Width. Numbers of combination may vary up to 6 to 7 values.
    Last edited by Darshan Shah; 06-11-2020 at 11:47 PM.

  9. #9
    Registered User
    Join Date
    05-23-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    57

    Re: How to make the combination of numbers and total of that combination should be in rang

    Yes Alf. We are very close to my requirements but as this model will be use by down stream labors, they wont be able to change this values in macro.
    Is it possible to give a "Cell reference" to that range? So by changing cell values, it will give desire result?

    Appreciate Kaper's macro

  10. #10
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Question Re: How to make the combination of numbers and total of that combination should be in rang


    Darshan,

    according to your sample in the initial post for a target between 150 & 300 :
    can a single value like 200 be a result or each result must contain at least two values like 200 = 105 + 95 ?

  11. #11
    Registered User
    Join Date
    05-23-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    57

    Re: How to make the combination of numbers and total of that combination should be in rang

    Marc,

    In a sample file, i kept a target just for the understanding the bounders of output. It will be vary. In some case, target will be 150 & 300, in some case it will be 100 & 250. But the result must contain all value which sum up equals to or within those range.

    200 = 105 + 95, 200 = 100 + 100, 200 = 150 + 50 + 50.

    All possible combinations are required.

    Kaper's macro is working wonderful in this case. All i need is variable target inputs from sheet only (For ease of operation). He has given that from macro.

  12. #12
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Question Re: How to make the combination of numbers and total of that combination should be in rang


    As I was expecting Yes or No as an answer to my question so you forgot 200 = 200 or ?

  13. #13
    Registered User
    Join Date
    05-23-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    57

    Re: How to make the combination of numbers and total of that combination should be in rang

    Marc,

    Do you have any other suggestion?

  14. #14
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: How to make the combination of numbers and total of that combination should be in rang

    Is it possible to give a "Cell reference" to that range? So by changing cell values, it will give desire result?
    You could use something like this

    a_mod.jpg

    and then you need to change macro to this

    Please Login or Register  to view this content.
    but the best solution would to ask Kaper please to change his original macro to make it more "user friendly" in order to change the max and min values.

    Alf
    Last edited by Alf; 06-13-2020 at 03:57 AM.

  15. #15
    Registered User
    Join Date
    05-23-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    57

    Re: How to make the combination of numbers and total of that combination should be in rang

    Yes Alf!

    Hurreeyyyy!! It's working. Thank you So much!

    Appreciate all of you for your kind support. Thank you all of you.

  16. #16
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: How to make the combination of numbers and total of that combination should be in rang

    You are welcome and thanks for feedback.

    Don't forget to mark your post "Solved". In your first post click on "Thread tools" and select "Mark thread solved"

    You should also click on the * in Kaper's post (left at bottom) and give him his well deserved rep and a message telling him you are grateful for his help because he is really the one who solved your problem.

    Alf

  17. #17
    Registered User
    Join Date
    05-23-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    57

    Re: How to make the combination of numbers and total of that combination should be in rang

    Yes Alf,

    Thread marked as Solved.

    Thank you for your guidance.

    In case, regarding this model, if i need to explore more, should i continue to ask help in this thread only or have to go for the new thread?

  18. #18
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: How to make the combination of numbers and total of that combination should be in rang

    Quote Originally Posted by Darshan Shah View Post
    Do you have any other suggestion?
    Just well read questions in order to well answer !
    Like since post #7 I asked twice for the minimum without any clear answer
    so I didn't post any of my combinatorics engines.
    But according to your initial samples for such few combinations - 55 or 57 depending on the minimum -
    the result must be instant whatever the code used …


    You can continue to ask help here for the same subject on same context …

  19. #19
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: How to make the combination of numbers and total of that combination should be in rang

    Quote Originally Posted by Darshan Shah View Post
    Do you have any other suggestion?
    Just well read questions in order to well answer !
    Like since post #7 I asked twice for the minimum without any clear answer so I didn't post any of my combinatorics engines.
    But according to your initial sample for such few combinations - 55 or 57 depending on the minimum -
    the result must be instant whatever the code used …


    You can continue to ask help here for the same subject on same context …

  20. #20
    Registered User
    Join Date
    05-23-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    57

    Re: How to make the combination of numbers and total of that combination should be in rang

    Okay Marc. Thank you.

  21. #21
    Registered User
    Join Date
    05-23-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    57

    Re: How to make the combination of numbers and total of that combination should be in rang

    Hello guys,

    I need some more help in this Model.

    Attachment 682603


    Attachment 682604

    Attachment 682605


    Attaching herewith the screen shot and Sample file for reference.

    Constraint is to match ordered number of reels v/s combination of reels.

  22. #22
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: How to make the combination of numbers and total of that combination should be in rang

    Can't see any of the attachment.

    Try edit your post #21 and delete your attachment then add it again and save. If you now can see the attachment then everybody else should see it. Should work for images.

    I had this problem some time ago and got help from a forum member to fix this problem.

    https://www.excelforum.com/suggestio...ttachment.html

    Alf

  23. #23
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: How to make the combination of numbers and total of that combination should be in rang

    I think there is a limit of 2 attachments per post. Moreover the main (and best working) method to attach files is:

    Scroll down your post and press Go Advanced button and then scroll down and press Manage Attachments link. Now follow the instructions at the top of that pop-up screen.

    PS. Remember to provide attachment with dummy, but representative data and layout.

  24. #24
    Registered User
    Join Date
    05-23-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    57

    Re: How to make the combination of numbers and total of that combination should be in rang

    Yes. I got it.

    Attaching here with the Sample file.

    Thank you for the guidance.
    Attached Files Attached Files

  25. #25
    Registered User
    Join Date
    05-23-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    57

    Re: How to make the combination of numbers and total of that combination should be in rang

    Alf, please try downloading that sample file from post #24.

  26. #26
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: How to make the combination of numbers and total of that combination should be in rang

    The explanantion is not clear, so I did what I guessed is your requirement.
    As there is word TOTAL below sizes list i changed one line in macro:
    Please Login or Register  to view this content.
    The rest is done just with standard solver (see file and note goal cell, and constraints) and standard formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    to count number of all produced reels.
    (Column K values at the start of solver were all set to 1)
    Attached Files Attached Files

  27. #27
    Registered User
    Join Date
    05-23-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    57

    Re: How to make the combination of numbers and total of that combination should be in rang

    Dear Kaper,

    Regret for the less explanation of my requirements.

    Attaching here with the sample file with more clarification of my requirement. Kindly go through it and revert if still something is missing.

    Firstly, go into the "Product" Tab, and then to the "Main Sheet" for better understanding.
    Attached Files Attached Files

  28. #28
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: How to make the combination of numbers and total of that combination should be in rang

    But have you tested the file attached to my previous post?

  29. #29
    Registered User
    Join Date
    05-23-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    57

    Re: How to make the combination of numbers and total of that combination should be in rang

    Yes Sir. I tried. But when i change order quantity and run the solver, output does not changed.

  30. #30
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: How to make the combination of numbers and total of that combination should be in rang

    No attachment, so I cannot check what happened. Probably you just ran macro, but not used solver.

  31. #31
    Registered User
    Join Date
    05-23-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    57

    Re: How to make the combination of numbers and total of that combination should be in rang

    I ran solver too. It is working. But our concern will is the range given in solver will be keep changing. Can we make it dynamic? Or is there any option to have macro instead of Solver? Because Solver is taking too long to give output.

    Further, have checked sample file attached in #27? Still my requirements are confusing?

  32. #32
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: How to make the combination of numbers and total of that combination should be in rang

    So let me summarize:

    Dynamic - sure, we could do that.
    Solver takes too long - no good alternatives if we understand the problem the same way (see below).
    My main observation from attachment in post #5 is in making smaller rims after cutting. So it could be simply achieved just by dividing each required number by 5

    And now how I understand the whole task.

    There is order for some items of length L in quantities Q,
    We try to use our source to fit sets (combinations) S of items that way that sum of L of items in each set is between lower and upper limit.

    Finding such sets was first step
    next step is finding such numbers N of each set, that the total quantities of each length produced is not larger than quantities ordered for each L.
    We shall remember that each N in given set gives 5 items of each of lengths L represented in this set.

    Do I understand it right?

    By the way, why you said in post #1 about combinations without repetitions?
    Let's look at very simple case:
    if the limits are 368 - 370 and order is
    length 15: 100 pcs
    length 209: 25 pcs

    The best option would be to cut just 5 reels into 209 15 15 15 15 sets. Which gives exactly the ordered amount.
    If we stick to no repetitions - we will end up with 0 produced.

  33. #33
    Registered User
    Join Date
    05-23-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    57

    Re: How to make the combination of numbers and total of that combination should be in rang

    Yes Kaper.

    Your understanding is perfectly right. Attaching here with the other sample file in which i have formalize some data as per our requirement.

    I ran macro first for the combinations, and than i ran solver to get the sets. But due to large data base, solver took a long time. I stopped solver after 30 mins and get the result as attached.

    In this case, 36 sizes are there in order. It may be possible we get 100 sizes in order and we need to match as per the quantity.

    Let's look at very simple case:
    if the limits are 368 - 370 and order is
    length 15: 100 pcs
    length 209: 25 pcs


    For this above, Yes we can also go for 209 15 15 15 15 - 25 sets so we will get quantity of 209 - 25 reels & 15 - (25*4=100) 100 reels.

    But, for that we have to add 15 size in "Size Column" (Here it is Column A:A) 4 times. Right? or is there any inter calculation method we can use to get this output (209 15 15 15 15) by giving input of 15 size once?
    Attached Files Attached Files

  34. #34
    Registered User
    Join Date
    05-23-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    57

    Re: How to make the combination of numbers and total of that combination should be in rang

    Kaper, any lead? Please let me know if more details are require..

  35. #35
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: How to make the combination of numbers and total of that combination should be in rang

    The numerical complexity in such problems increase very rapidly with the elements number.
    So it's not strange it takes that long.
    And the more variables there is, the slower and less effective is Solver. With this 150 or so combinations you are already quite close to Solver limits.

    Nevertheless, As I wrote about it. I've prepared a single sheet where it all occurs.

    The code has added parts which write formulas into a sheet. Also the code performs solver setup (there has to be a reference to Solver in VBA - see screenshot). As a matter of fact - does it several times, as i asked solver to first look for using these lengths which are present only in few combinations. I also changed used engine (method) to evolutionary.

    See the extended code and the file.

    At the beginning only A6:Bxxx values are needed. Please don't write anything below last required length (so for sample data in row 36). Also do not insert lengths wit 0 pcs needed (in column B). And also values in I1:I2. The rest of cells can be empty or left as it was from previous usage.

    Please Login or Register  to view this content.
    Attached Images Attached Images

  36. #36
    Registered User
    Join Date
    05-23-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    57

    Re: How to make the combination of numbers and total of that combination should be in rang

    Appreciate your code Kaper. Especially that message box indicating total time of model run.

    Attaching the working file (sample_4) with your new code. Produced reels are 1413 against the total ordered reel 1895. So output is around 75%.

    I think considering the inter matching combinations may improve this %. Internal combinations like 180 + 180 = 360 , 185 + 185 = 370 and many more. Is it possible to consider these internal combinations also?
    Attached Files Attached Files

  37. #37
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: How to make the combination of numbers and total of that combination should be in rang

    Sp dnia od exactly what i asked for - combinations with repetitions

  38. #38
    Registered User
    Join Date
    05-23-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    57

    Re: How to make the combination of numbers and total of that combination should be in rang

    Yes Kaper. We can go for it too. I meant at that time, No repetitions means - only single pattern like 180 + 185 = 365 should come. Not the other one i.e 185 + 180 = 365. Same thing. I guess that point was confusing. Regret for that.

  39. #39
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: How to make the combination of numbers and total of that combination should be in rang

    We can go for it, but (for sample data) solver can't it will end up with more than 200 combinations, so the number of variables will be more than solver upper limit.
    There is a software called open solver - but I have no experience with this one.

    As for allowing repetitions (as I said reducing input data, to have not more than 200 combinations) - only one line has to be changed in a code:
    Please Login or Register  to view this content.
    it is in
    Please Login or Register  to view this content.

+ 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. Replies: 0
    Last Post: 09-12-2018, 10:42 AM
  2. [SOLVED] VBA to Make Excel Find Combination of Numbers that Equal a Different Number
    By OpieWinston in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-11-2016, 01:01 PM
  3. Replies: 8
    Last Post: 09-10-2015, 03:43 PM
  4. Combination of numbers that give a single total
    By Jorgeewa in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-24-2014, 02:22 AM
  5. Replies: 0
    Last Post: 04-18-2012, 01:56 PM
  6. Find the combination of numbers that when added equal a reqired total??
    By Handsy11 in forum Excel Formulas & Functions
    Replies: 41
    Last Post: 09-06-2005, 03:05 PM
  7. [SOLVED] Total a Combination of Numbers
    By Erika in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-19-2005, 07:06 PM

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