hi!
I agree with the above answers. Its complicated.
One question is how mane combiantion of each you want.'
example.
Heres a set up I made to give you all the possible answers you like. Just set up the worksheet as per yellow color. they are needed data.
like this
comb-perm2.xls |
---|
|
---|
| A | B | C | D | E | F |
---|
1 | list | DesiredSum | 20 | | possiblecombination | Sum |
---|
2 | 15 | | | | 8+3+9= | 20 |
---|
3 | 26 | | | | 15+2+3= | 20 |
---|
4 | 8 | | | | 6+2+3+9= | 20 |
---|
5 | 6 | | | | 8+6+2+4= | 20 |
---|
6 | 2 | | | | | |
---|
7 | 3 | | | Input | | |
---|
8 | 4 | | | Result | | |
---|
9 | 9 | | | | | |
---|
|
---|
And run this code!<font face=Courier New><SPAN style="color:#00007F">Option</SPAN><SPAN style="color:#00007F">Explicit</SPAN><SPAN style="color:#00007F">Dim</SPAN> n, k, q<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Double</SPAN><SPAN style="color:#00007F">Dim</SPAN> Comblist()<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN><SPAN style="color:#00007F">Dim</SPAN> b()<SPAN style="color:#00007F">Dim</SPAN> Ans()<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Variant</SPAN><SPAN style="color:#00007F">Private</SPAN><SPAN style="color:#00007F">Sub</SPAN> Comb(j, m<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Integer</SPAN>)<SPAN style="color:#00007F">Dim</SPAN> tmp, X
<SPAN style="color:#00007F">If</SPAN> j > n<SPAN style="color:#00007F">Then</SPAN>
tmp = ""
<SPAN style="color:#00007F">For</SPAN> X = 1<SPAN style="color:#00007F">To</SPAN> n
tmp = tmp & b(X)
<SPAN style="color:#00007F">Next</SPAN> X
q = q + 1
<SPAN style="color:#00007F">ReDim</SPAN><SPAN style="color:#00007F">Preserve</SPAN> Comblist(UBound(Comblist) + 1)<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN>
Comblist(UBound(Comblist)) = tmp
<SPAN style="color:#00007F">Else</SPAN>
<SPAN style="color:#00007F">If</SPAN> k - m< n - j + 1<SPAN style="color:#00007F">Then</SPAN>
b(j) = 0
Comb j + 1, m
<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">If</SPAN> m< k<SPAN style="color:#00007F">Then</SPAN>
b(j) = 1
Comb j + 1, m + 1
<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN><SPAN style="color:#00007F">Sub</SPAN> Sumxxx()<SPAN style="color:#00007F">Dim</SPAN> St, i, j, tmp, Tmp2, AnsCount, L, Res<SPAN style="color:#00007F">Dim</SPAN> listx()
n = Range("a65536").End(xlUp).Row - 1
Res = Range("c1").Value
q = 0<SPAN style="color:#00007F">ReDim</SPAN> Comblist(0)<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN><SPAN style="color:#00007F">ReDim</SPAN> b
<SPAN style="color:#00007F">ReDim</SPAN> listx
<SPAN style="color:#00007F">ReDim</SPAN> Ans(0)<SPAN style="color:#00007F">For</SPAN> k = 1<SPAN style="color:#00007F">To</SPAN> n
Comb 1, 0<SPAN style="color:#00007F">Next</SPAN> k<SPAN style="color:#00007F">For</SPAN> i = 1<SPAN style="color:#00007F">To</SPAN> n
listx(i) = Range("a" & 1 + i).Value<SPAN style="color:#00007F">Next</SPAN> i
AnsCount = 0<SPAN style="color:#00007F">For</SPAN> i =<SPAN style="color:#00007F">LBound</SPAN>(Comblist)<SPAN style="color:#00007F">To</SPAN><SPAN style="color:#00007F">UBound</SPAN>(Comblist)
St = ""
tmp = 0
<SPAN style="color:#00007F">For</SPAN> j = 1<SPAN style="color:#00007F">To</SPAN> Len(Comblist(i))
tmp = tmp + Mid(Comblist(i), j, 1) * listx(j)
<SPAN style="color:#00007F">If</SPAN> Mid(Comblist(i), j, 1) = 1<SPAN style="color:#00007F">Then</SPAN>
St = St & "+" & listx(j)
<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> j
<SPAN style="color:#00007F">If</SPAN> tmp = Res<SPAN style="color:#00007F">Then</SPAN>
<SPAN style="color:#00007F">ReDim</SPAN><SPAN style="color:#00007F">Preserve</SPAN> Ans(UBound(Ans) + 1)
AnsCount = AnsCount + 1
Ans(AnsCount) = St
<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">Next</SPAN> i
Range("e2:f65536").Value = ""<SPAN style="color:#00007F">For</SPAN> i =<SPAN style="color:#00007F">LBound</SPAN>(Ans) + 1<SPAN style="color:#00007F">To</SPAN><SPAN style="color:#00007F">UBound</SPAN>(Ans)
Range("e" & i + 1).Value = "'" & Mid(Ans(i), 2, Len(Ans(i))) & "="
Range("F" & i + 1).Value = "=" & Ans(i)<SPAN style="color:#00007F">Next</SPAN> i<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>
Note. the more list in Column A you have the slower the code will return.
If you got more than 30 entries it will take sometimes to return,and it will be a cold day in hell before youl see the combination of 100 and up.