how to find all combinations that equal a given sum

01-24-2018,01:13 PM

Hi
I want to know how can I discover all possible combinations from given list of numbers.
A Image is attached for reference
Whatever formula or VBA code
Attached excel file can find only one possible combination
I want multiple solutions/combinations equally image fastened in excel file.

01-24-2018,01:xxx PM

Here is a file with Harlan Grove'south lawmaking - printing the push button and select the ranges when prompted. Note that if you have upward of 20 or so values, the lawmaking can accept a loooooooooong time to run.

01-24-2018,01:43 PM

First-class Back up and solution
Give thanks you

01-24-2018,01:43 PM

While this can be washed, its something that is asked alot. Excel is really the wrong tool for this task.

In a pocket-size sample set similar you have provided, the number of combinations to bank check is relatively low. If you data gear up is much larger y'all may be in for a surprise.

Mathematically, in the sample you provided with eight numbers, your possible combinations to cheque 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 tin can accept a really, really really long time to complete and can hands 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 affair for ~30 values (may have been some criteria dont retrieve 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, and then it can only practise one cheque at a time instead of trying x combinations simultaneously.

Its possible to speed things upwards drastically if you put constraints on what to return, like the kickoff x number of combinations. Something like that tin drastically reduce the time and endeavor to cycle through.

01-24-2018,01:45 PM

Expert solution likewise thanks for quick response
thanks all for solutions

01-24-2018,01:47 PM

I am going to increase the range to almost 200 numbers and i will share my experience with yous soon

01-24-2018,01:50 PM

Quote Originally Posted by JohnTopley View Post

See attached.

What if I increase the range to 200 numbers or 300 numbers will this work fine ?

01-24-2018,01:52 PM

I'g non sure if the we will still be alive by the fourth dimension Excel finishes with 200 possible numbers..... If you limited the count of numbers used to brand the total to a small-scale number, then mayhap....

01-24-2018,02:07 PM

If you are looking to add 2 or 3 or 4 numbers - like matching invoices totals to items purchased, and then you would have a chance. If you lot are looking to add up to 100 numbers to reach your total, and so you are out of luck.

01-24-2018,02:31 PM

As already noted you will be waiting for e'er with the solution I posted!!!

I have only tried it with < 20 numbers of limited range.

01-24-2018,03:18 PM

Context would likely help usa help y'all. 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 y'all 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 piece of cake to encompass and ask but mathematically very taxing to do. Excel simply wasnt made for that blazon of matter. As I remember from my example we concluded up convincing the customer to do it in Access equally some characteristic it it was able to exercise this within seconds/minutes instead of hours/days/never. I dont mess with Access so I couldnt say how to do it there.

And over again, adding some constraints could drastically speed things up like stopping after x matches for example. Context would shed calorie-free on if that feasible or non.

With 200 numbers, you are looking at 200^200 possible combinations or one.6069380442589902755419620923412e+460 combinations according to Win calc if it can even handle numbers as large as information technology would be. Excel would have to try each of those combinations ane at a time, check if it equals your target, shop it if information technology does or know to skip it if it doesnt and so effort the next. Its equally dull as it sounds.

01-24-2018,05:34 PM

It's not as bad as all that - it is really only on lodge of 200! and not 200^200, reduced by other constraints like order non being important and stopping when the sum exceeds the target value. Yet long plenty that the heat death of the universe volition probably occur earlier it is done

01-27-2018,03:26 AM

ok I would like to articulate the motive for this question
First of all its non my homework . Its totally for religious purpose or social . According to my organized religion every name have specific some of digits e.g 672 is the digit for my name. nosotros have 100 unique names for it and every name accept different value . in one case we notice the value of our proper noun and then we await the combination for ou proper name from those 100 names/value .. and so we choose all-time one for our luck ... you lot 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

Quote Originally Posted past Zer0Cool View Post

Context would likely help us help you. Knowing why you desire to do this may present the take chances to utilise an culling method or incorporate something into the provided solution to make information technology practical. So why do you lot need to do this? What are you going to do with all those combinations? Is there something specific in those combinations y'all are looking for?

Reality is, the basic question of "Finding all combinations that equal a value" is very easy to embrace and enquire simply mathematically very taxing to practise. Excel simply wasnt fabricated for that type of affair. As I recall from my instance we concluded up convincing the client to do it in Access as some feature it it was able to exercise this within seconds/minutes instead of hours/days/never. I dont mess with Access so I couldnt say how to do it at that place.

And again, calculation some constraints could drastically speed things upwardly like stopping afterwards x matches for case. Context would shed calorie-free 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 information technology tin can even handle numbers as large as it would be. Excel would accept to effort each of those combinations 1 at a time, bank check if it equals your target, store it if it does or know to skip it if it doesnt and then effort the next. Its every bit ho-hum equally it sounds.

01-27-2018,03:l AM

Don't quote whole posts -- it's just ataxia. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes articulate to whom and what you are responding

For normal conversational replies, try using the QUICK REPLY box below.

01-28-2018,10:52 AM

In the fastened, put your list of names in cavalcade A starting in A3, and their number values in B starting in B3. Put your target value in C2 and click the push button. And be patient. Start with a smaller gear up of your actual names to go a experience for how long it might take. This volition find the combinations of 3 ,4, or 5. I ignored 2 but there may be some solutions for combinations of two - you lot could apply formulas to effigy those out easily, though.

07-27-2018,06:10 AM

Hullo!
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 at that place a style of calculation a column for business relationship numbers relating to the values and when the results are produced on the "find sum solutions" canvass that it too requite southward 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 than fine tuned for this detail purpose?
Awesome solution anyway thanks!!

07-27-2018,06:13 AM

Welcome to the forum!

Unfortunately your mail does not comply with Rule two of our Forum

RULES. Do not post a question in the thread of another fellow member -- start your own thread.

If yous experience an existing thread is particularly relevant to your need, provide a

link to the other thread in your new thread.

Old threads are often but monitored by the original participants. New threads not only open you lot up to all possible participants again, they typically get faster response, too.

ten-22-2018,02:36 PM

Quote Originally Posted by Bernie Deitrick View Post

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 push. And exist patient. Start with a smaller set of your actual names to get a experience for how long it might have. This volition discover the combinations of iii ,4, or five. I ignored 2 just at that place may be some solutions for combinations of two - you could utilise formulas to effigy those out easily, though.

How-do-you-do there. I downloaded your file Name combos.xlsm‎ and tried to rework it using my values but information technology wants me to debug the code or abort. I don't run into an option to upload my file simply 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 upwards to 17000. When I click debug it takes me to this line in the code - iTarget = Cells(2, "C").Value Any chance y'all can advise me on how to get my figures to work in your model?

10-22-2018,03:13 PM

Quote Originally Posted by cutty382 View Post

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 meet an option to upload my file but the changes I made were calculation 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 information technology takes me to this line in the lawmaking - iTarget = Cells(2, "C").Value Whatever chance you lot can propose me on how to go my figures to work in your model?

I institute the answer. I had to change your code from INTEGER to LONG and it ran.

12-03-2018,x:05 AM

Thanks then much!! but explanation maybe be helpful as well!!

12-22-2019,12:04 PM

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?

12-22-2019,12:22 PM

Authoritative Notation:

Welcome to the forum.

We are happy to help, however whilst y'all feel your request is similar to this thread, feel has shown that things soon become confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Delight come across

Forum Rule #iv nearly hijacking and showtime a new thread for your query.

If you are not familiar with how to kickoff a new thread run into the FAQ: How to kickoff a new thread

02-03-2020,02:46 AM

Dear Sir,
Delight provide excel sheet for cricket fantasy game Dream11 team for all possible combinations

02-03-2020,03:29 AM

Quote Originally Posted by Zer0Cool View Post

. . . Mathematically, in the sample you provided with 8 numbers, your possible combinations to check is something like viii^eight combinations if I am non mistaken . . .

Given N numbers viewed as a set, there are ii^N subsets including the empty set and the entire prepare. Since the empty set need non be checked, at that place are 2^N-1 subsets to sum, though the one-element subsets and the unabridged set are straightforward. You simply need to come up with sums of subsets with upwardly to ROUNDUP(Due north/2,0) elements because sums of subsets with more elements would exist given by SUM(entire_set) - SUM(subsets w/up to ROUNDUP(Due north/two,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.

02-03-2020,03:52 AM

@ Ramsrahi
Administrative Notation:

Welcome to the forum.

We are happy to help, however whilst you lot experience your request is similar to this thread, experience has shown that things soon go disruptive when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Delight see

Forum Dominion #4 about hijacking and start a new thread for your query.

If you lot are not familiar with how to get-go a new thread come across the FAQ: How to start a new thread

03-29-2020,09:07 AM

Quote Originally Posted by Bernie Deitrick View Post

In the attached, put your list of names in cavalcade A starting in A3, and their number values in B starting in B3. Put your target value in C2 and click the push button. And exist patient. Beginning with a smaller prepare of your actual names to become a feel for how long it might take. This will find the combinations of iii ,4, or v. I ignored two but there may exist some solutions for combinations of 2 - y'all could use formulas to figure those out easily, though.

Hello bernie, thanks, merely what I was looking for. Know this is an onetime post but whatever chance I could utilise the aforementioned for a combination of 6 and 7s? No idea how to practise this. But possible you could upload a similar excel file with those combinations? ( 6 and 7 that is). Would actually appreciate information technology. Thanks in advance.

03-29-2020,09:11 AM

Authoritative Annotation:

Welcome to the forum.

Nosotros are happy to assistance, even so whilst you feel your request is like 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 run across

Forum Rule #4 near hijacking and start a new thread for your query.

If yous are non familiar with how to start a new thread see the FAQ: How to beginning a new thread

how to find all combinations that equal a given sum

Find All Combinations That Equal A Given Sum

Re: Observe All Combinations That Equal A Given Sum

Re: Find All Combinations That Equal A Given Sum

Re: Detect All Combinations That Equal A Given Sum

Re: Find All Combinations That Equal A Given Sum

Re: Find All Combinations That Equal A Given Sum

Re: Observe All Combinations That Equal A Given Sum

Re: Observe All Combinations That Equal A Given Sum

Re: Notice All Combinations That Equal A Given Sum

Re: Find All Combinations That Equal A Given Sum

Re: Discover All Combinations That Equal A Given Sum

Re: Discover All Combinations That Equal A Given Sum

Re: Find All Combinations That Equal A Given Sum

Re: Find All Combinations That Equal A Given Sum

Re: Find All Combinations That Equal A Given Sum

Re: Observe All Combinations That Equal A Given Sum

Re: Find All Combinations That Equal A Given Sum

Re: Detect All Combinations That Equal A Given Sum

Re: Notice All Combinations That Equal A Given Sum

Re: Find All Combinations That Equal A Given Sum

Re: Find All Combinations That Equal A Given Sum

Re: Find All Combinations That Equal A Given Sum

Re: Find All Combinations That Equal A Given Sum

Re: Find All Combinations That Equal A Given Sum

Re: Find All Combinations That Equal A Given Sum

Re: Notice All Combinations That Equal A Given Sum

Re: Find All Combinations That Equal A Given Sum

Re: Find All Combinations That Equal A Given Sum

Re: Discover All Combinations That Equal A Given Sum

0 Response to "how to find all combinations that equal a given sum"

Post a Comment

Iklan Atas Artikel

Iklan Tengah Artikel 1

Iklan Tengah Artikel 2

Iklan Bawah Artikel