Hi All
I have identified the number of characters in column A by using LEN function
1. How do i create a different combination in excel so that the total sum of characters add up to 5000 and highlight them?
2. If 1. is not possible to sum up the total number of characters into 5000, how do we create a dynamic formula where the number of characters add up close to 5000 and highlight them?
Thanks in advance.
do you have a list of all the individual items.
Unless you have a different algorithm in mind, this appears to me to be a "subset sum" problem. The usual first thing to try in Excel is to see if Solver will solve it. I outline a procedure here: https://www.excelforum.com/excel-for...ml#post4542462
Here's a VBA procedure: http://www.tushar-mehta.com/excel/te...e_combinations
See if those will help with the problem.
Originally Posted by shg
Hi Mr Shorty
I tried using solver to get the combinations, it provided a message that there are too many variable cells.
You can install OpenSolver, a free and open source add-in that has no limits on the number of variables.
If you setup Solver in your worksheet, once you install OS it will automatically load the model.
HTH,
Francesco
Aim high or don't even try.
---------------------------------
If your question has been answered, don't forget to mark the thread as SOLVED.
If you find an answer helpful, click on the star icon at the bottom of the post.
So once installed, i click on the cell which is formulated and i click on the icon which shows "Show/Hide Model"?
Please refer to the attachment for your reference.
Here is a model using OpenSolver.
Download OpenSolver at:
https://opensolver.org/
As your spreadsheet is a bit difficult to read I've added a macro (MultipleSearch)that finds the addresses where values are set to 1 and writes the cell addresses from
G299 and downwards.
Alf
Ps Hi Francesco all well with you I hope.
Your setup was incorrectly defining binary variables in column B instead of C.
I have also added a constraint on the max number of choiches (items in the list) based on the min length, and a conditional formatting to highlight chosen cells.
Please check the menus under Model and Solve and the online help.
HTH,
Francesco
Edit: Hi Alf, beat me to it for a few minutes.
How about extracting the values that you need to run a solver setup? I've tested a macro "ConDensRange" that extracts row number and Len value to Sheet1.
As the solver model is set up on Sheet1 you just run OpenSolver after extracting row number and Len value.
Alf
A last warning using solver for this kind of problem. Solver will only find a solution when told to find a target value when in reality there may exist quite a number of
possible solutions that match your target i.e. 5000
To illustrate this I've uploaded a file where one can specify a list of numbers and give a target value as well s the number of target values to be found. The macro is written by
Tushar Metha and forum member John Tophely added a more user friendly interface.
I've taken the Len values from file "pending for índex .... mod2.xlsm" Sheet1 and pasting it to range D4:D293 then sorting this range from lowest to highest and adding
a running number in column E for identification. D3 is the target value and D2 is the number of solutions one wish to find. In this case I wanted 40.
To find all possible solutions put a 0 in D2. Select range D2:D293 and then click button run.
The first value in the F column 5000,20:40:06,1,2,3,4,73 is target value, time when macro was run, and the 5 values that added together match the target value.
Looking at the 5 values in the H and I column the numbers are identical but if you check the F column values you see that the last value in one case is 73 and the next one is
74. Checking the indices (E column) you see that 73 is 1215 but so is also 74.
What combination of Len numbers that make up to 5000 is the one you are looking for? Solver will only give you one but will this be the right one?
Alf
Good questions. But in fact, if you don't specify your preferences more strictly, any solution is formally equivalent to the alternative ones and there is no explicit reason to care about the full set of solutions.
Przemyslaw Kowalik, Lublin Univ. of Technology
Is it possible to get near by value of Target Value (Say 5000 in your case)?
Like, if i set 5000 as a target and run this solver and get combination of even 4999 or 5001?
@Darshan Shah
Yes, it's possible to add such constraints (or, if you prefer such names, to specify the target values as an interval, not a single number). If you consider an example created by Hydraulics and OpenSolver this is what should be done:
1) Change "optimisation type" from target value to either maximise or minimise (it doesn't matter).
2) Clear the Objective Cell field.
3) Add two new constraints D1<=5001 and D1>=4999 (or whatever lower and upper bound you want).
I changed Hydraulics's file and ran on Excel 2019 and OpenSolver 2.9.0 and it works.
A more general remark.
Both standard Excel Solver and OpenSolver have a feature of solving optimisation models in which the objective function (specified by a formula referenced in Set Cell/Objective Cell field) is neither maximised nor minimised but it must attain some target value instead. In fact it is not optimisation but rather searching a feasible solution (without any objective function) with one extra constraint "some formula named objective function = target value".
Last edited by PKowalik; 05-25-2020 at 06:15 AM.
Can we set minimum & maximum target value in this "find_sum2.xlsm" file? Any other code to get the result? Please Help.
Administrative Note:
Welcome to the forum.
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks