Hello
I want to know how can I find all possible combinations from given list of numbers.
A Image is attached for reference
Any formula or VBA code
Attached excel file can find only one possible combination
I want multiple solutions/combinations as image attached in excel file.
See attached.
Here is a file with Harlan Grove's code - press the button and select the ranges when prompted. Note that if you have upwards of 20 or so values, the code can take a loooooooooong time to run.
Bernie Deitrick
Excel MVP 2000-2010
Excellent Support and solution
Thank you
While this can be done, its something that is asked alot. Excel is really the wrong tool for this job.
In a small sample set like you have provided, the number of combinations to check is relatively low. If you data set is much larger you may be in for a surprise.
Mathematically, in the sample you provided with 8 numbers, your possible combinations to check is something like 8^8 combinations if I am not mistaken (I am sleepy could be wrong). In a larger data set it could get exponentially larger. This basically means it can take a really, really really long time to complete and can easily surpass the amount of space a single column and/or sheet provides for results. A while back I had a customer as for the same thing for ~30 values (may have been some criteria dont recall specifics). Our code, as optimized as possible, was running 10 hours later with no end in sight. The problem is without constraints it has to check all possible combinations to exhaust the correct results.
One of the issues is VBA is single threaded, so it can only do one check at a time instead of trying x combinations simultaneously.
Its possible to speed things up drastically if you put constraints on what to return, like the first x number of combinations. Something like that can drastically reduce the time and effort to cycle through.
Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.
"I am here to help, not do it for people" -Me
Good solution too thanks for quick response
thank you all for solutions
I am going to increase the range to almost 200 numbers and i will share my experience with you soon
What if I increase the range to 200 numbers or 300 numbers will this work fine ?Originally Posted by JohnTopley
I'm not sure if the we will still be alive by the time Excel finishes with 200 possible numbers..... If you limited the count of numbers used to make the total to a small number, then maybe....
my real problem is that I have 100 numbers which have different values like 1436 , 2
i want to find all possible combination for a specific number from all given numbers.
If you are looking to add 2 or 3 or 4 numbers - like matching invoices totals to items purchased, then you would have a chance. If you are looking to add up to 100 numbers to reach your total, then you are out of luck.
As already noted you will be waiting for ever with the solution I posted!!!
I have only tried it with < 20 numbers of limited range.
Context would likely help us help you. Knowing why you want to do this may present the chance to use an alternative method or incorporate something into the provided solution to make it practical. So why do you need to do this? What are you going to do with all those combinations? Is there something specific in those combinations you are looking for?
Reality is, the basic question of "Finding all combinations that equal a value" is very easy to comprehend and ask but mathematically very taxing to do. Excel simply wasnt made for that type of thing. As I recall from my example we ended up convincing the customer to do it in Access as some feature it it was able to do this within seconds/minutes instead of hours/days/never. I dont mess with Access so I couldnt say how to do it there.
And again, adding some constraints could drastically speed things up like stopping after x matches for example. Context would shed light on if that feasible or not.
With 200 numbers, you are looking at 200^200 possible combinations or 1.6069380442589902755419620923412e+460 combinations according to Win calc if it can even handle numbers as large as it would be. Excel would have to try each of those combinations 1 at a time, check if it equals your target, store it if it does or know to skip it if it doesnt and then try the next. Its as tedious as it sounds.
It's not as bad as all that - it is really only on order of 200! and not 200^200, reduced by other constraints like order not being important and stopping when the sum exceeds the target value. Still long enough that the heat death of the universe will probably occur before it is done
ok I would like to clear the motive for this question
First of all its not my homework . Its totally for religious purpose or social . According to my religion every name have specific some of digits e.g 672 is the digit for my name. we have 100 unique names for it and every name have different value . once we find the value of our name then we look the combination for ou name from those 100 names/value .. then we choose best one for our luck ... you can say astrology type thing it is.
I wish it would not go for long sets like {v1,v2,v3,v4,v5,v6,v7,v8} = SUM.... sets or combinations should be small
Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding
For normal conversational replies, try using the QUICK REPLY box below.
In the attached, put your list of names in column A starting in A3, and their number values in B starting in B3. Put your target value in C2 and click the button. And be patient. Start with a smaller set of your actual names to get a feel for how long it might take. This will find the combinations of 3 ,4, or 5. I ignored 2 but there may be some solutions for combinations of two - you could use formulas to figure those out easily, though.
Last edited by Bernie Deitrick; 01-28-2018 at 11:04 AM.
Hello!
I've stumbled upon this response and the attached Macro and find it really helpful.
I've been looking for something like this from Credit Control perspective whereby we receive a payment for several invoices relating to several different accounts.
Is there a way of adding a column for account numbers relating to the values and when the results are produced on the "find sum solutions" sheet that it also give s the account numbers relating to the transaction values?
I can definitely just live with this solution but just checking if there is anything out there more fine tuned for this particular purpose?
Awesome solution anyway thanks!!
Welcome to the forum!
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
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.
Hi there. I downloaded your file Name combos.xlsm and tried to rework it using my values but it wants me to debug the code or abort. I don't see an option to upload my file but the changes I made were adding 4 Names and values, changing the target number to 60000 and changing all number values starting with 5500 with a range up to 17000. When I click debug it takes me to this line in the code - iTarget = Cells(2, "C").Value Any chance you can advise me on how to get my figures to work in your model?
I found the answer. I had to change your code from INTEGER to LONG and it ran.
Thank you so much!! but explanation maybe be helpful as well!!
I have 20 small numbers for which I want to find all of the combinations of 8 of the numbers that sum to 26, is this possible?
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
Dear Sir,
Please provide excel sheet for cricket fantasy game Dream11 team for all possible combinations
Given N numbers viewed as a set, there are 2^N subsets including the empty set and the entire set. Since the empty set need not be checked, there are 2^N-1 subsets to sum, though the 1-element subsets and the entire set are straightforward. You only need to come up with sums of subsets with up to ROUNDUP(N/2,0) elements because sums of subsets with more elements would be given by SUM(entire_set) - SUM(subsets w/up to ROUNDUP(N/2,0) elements). Which means you're trying to match sums of subsets with up to ROUNDUP(N/2,0) elements to target_sum or to SUM(entire_set) - target_sum.
@ Ramsrahi
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
Hello bernie, thanks, just what I was looking for. Know this is an old post but any chance I could use the same for a combination of 6 and 7s? No idea how to do this. But possible you could upload a similar excel file with those combinations? ( 6 and 7 that is). Would really appreciate it. Thanks in advance.
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks