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!
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!
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.
Best Regards,
Kaper
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?
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 ?!
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.
You should have no problem adapting Kaper's macro to suit your needs.
afind_value.jpg
Alf
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 ?
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.
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
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 ?
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.
As I was expecting Yes or No as an answer to my question so you forgot 200 = 200 or ?
Marc,
Do you have any other suggestion?
You could use something like thisIs it possible to give a "Cell reference" to that range? So by changing cell values, it will give desire result?
a_mod.jpg
and then you need to change macro to this
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.Please Login or Register to view this content.
Alf
Last edited by Alf; 06-13-2020 at 03:57 AM.
Yes Alf!
Hurreeyyyy!! It's working. Thank you So much!
Appreciate all of you for your kind support. Thank you all of you.
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
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?
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 …
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 …
Okay Marc. Thank you.
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.
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
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.
Yes. I got it.
Attaching here with the Sample file.
Thank you for the guidance.
Alf, please try downloading that sample file from post #24.
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:
The rest is done just with standard solver (see file and note goal cell, and constraints) and standard formulaPlease Login or Register to view this content.
to count number of all produced reels.Formula:Please Login or Register to view this content.
(Column K values at the start of solver were all set to 1)
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.
But have you tested the file attached to my previous post?
Yes Sir. I tried. But when i change order quantity and run the solver, output does not changed.
No attachment, so I cannot check what happened. Probably you just ran macro, but not used solver.
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?
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.
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?
Kaper, any lead? Please let me know if more details are require..
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.
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?
Sp dnia od exactly what i asked for - combinations with repetitions
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.
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:
it is inPlease Login or Register to view this content.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks