Permutation and combinations VB code to be modified

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,428
Hello Gurus,

I found this thread http://www.mrexcel.com/forum/showthread.php?t=172630 with a code in post #3 that almost gave me the answer for what I was lookking for. I don't know how to modify this code, so here I am asking your help.
This code gives all the possible combinations from a set of numbers. What want is for this to show only those combinations that totals to a desired result e.g the set of numbers is 1,2,3,4,5,6,7,8,9 and I want all combinations of three numbers that totals to 6. The answer is only 1,2 and 3. This is the ONLY combination with 3 numbers and every number is used opnly once.
A number in the set cannot be used twice which already is a part of the code. It selects one number only once but it doesn't give the total of all combinations.
Is there a way to get it to do what I want or is it not possible?
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,364
hi, i looked thru th e code and i came up with this:
replace this lines - SavePermutation SetMembers()
with this:
If Application.WorksheetFunction.Sum(SetMembers) = 6 Then SavePermutation SetMembers()
change the 6 to a cell value or something (for example cell B2) :
If Application.WorksheetFunction.Sum(SetMembers) = Range("B2").Value Then SavePermutation SetMembers()
 

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,428
It is not working for me :(

May be because when the other part of code works, it puts the combinations like this
and I think the sum function that you have included in new code does not sum them up to 6. So I end up getting no combinations at all.

How can I get the numbers to show up individually in different cells so that your code can sum them up?
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028

ADVERTISEMENT

See
Find a set of amounts that match a target value
http://www.tushar-mehta.com/excel/templates/match_values/index.html

Hello Gurus,

I found this thread http://www.mrexcel.com/forum/showthread.php?t=172630 with a code in post #3 that almost gave me the answer for what I was lookking for. I don't know how to modify this code, so here I am asking your help.
This code gives all the possible combinations from a set of numbers. What want is for this to show only those combinations that totals to a desired result e.g the set of numbers is 1,2,3,4,5,6,7,8,9 and I want all combinations of three numbers that totals to 6. The answer is only 1,2 and 3. This is the ONLY combination with 3 numbers and every number is used opnly once.
A number in the set cannot be used twice which already is a part of the code. It selects one number only once but it doesn't give the total of all combinations.
Is there a way to get it to do what I want or is it not possible?
 

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,428
Thanks a lot Tushar. It is a perfect solution for what I was doing. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,567
Messages
5,596,908
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top