Need help for a mathematical problem

[andrescane]

First of all, sorry for my english. This is the problem: given some number and a group of other numbers, i need to calculate which of these number are the result of an addition that have as a result the first number. For example: result 100, group of numbers 20, 80, 3, 5. The correct answer is: 20 and 80.
do anybody know if this problem can be resolved in excel??? Thanks.

 

[Yogi Anand]

The macro that Sixth Sense left works, but only with two numbers. I need that it works with many more. For example: the result is 100 and the numbers are 10, 20, 60, 35, 56, 10. The correct numbers are 10+20+60+10
Can anybody give me the solution?
Thanks

Hi andrescane:

Here is a solution using AdvancedFilter that might be of some interest to you ...

y040120h1a.xls
	A	B	C	D	E	F	G	H	I
1	Numbers	NumbersU		NumbersU	Numbers	100	NumbersU		intermediaryCalculation
2	10	102		102	10		102	TRUE	10
3	20	203		203	20		203	TRUE	30
4	60	604		604	60		604	TRUE	90
5	35	355		107	10		355	FALSE	0
6	56	566					566	FALSE	0
7	10	107					107	TRUE	0
8
9
10								source
11								criteria
12								copyto
Sheet3

Please post back to advise if it works for you -- otherwise explain a little further and then let us take it from there.

 

[SIXTH SENSE]

hi!
I thinnk this requires a combination algorithm.

take a look at this.

comb-perm2.xls
	A	B	C	D	E	F
1	Entries	1023	sum	100	possiblecombinations	10
2	10				+60+35+5
3	20				+60+35+5
4	60				+60+35+2+3
5	35				+10+20+60+10
6	56				+20+60+10+5+5
7	10				+10+20+60+5+5
8	5				+20+60+10+5+2+3
9	5				+20+60+10+5+2+3
10	2				+10+20+60+5+2+3
11	3				+10+20+60+5+2+3
Sheet2

the 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:#007F00">' & '"-"</SPAN>
        <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
        Range("b1").Value = q
    <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">Private</SPAN><SPAN style="color:#00007F">Sub</SPAN> CombinationButton_Click()<SPAN style="color:#00007F">Dim</SPAN> i
n = 6
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">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 =<SPAN style="color:#00007F">LBound</SPAN>(Comblist)<SPAN style="color:#00007F">To</SPAN><SPAN style="color:#00007F">UBound</SPAN>(Comblist)<SPAN style="color:#00007F">Next</SPAN> i<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN><SPAN style="color:#00007F">Private</SPAN><SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()<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("d1").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<SPAN style="color:#00007F">For</SPAN> i =<SPAN style="color:#00007F">LBound</SPAN>(Ans)<SPAN style="color:#00007F">To</SPAN><SPAN style="color:#00007F">UBound</SPAN>(Ans)
    Range("e" & 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: ignore combinationbutton1_click()

 

[TryingToLearn]

Thanks, but it seems to be a problem. It appears an error (I am translate from spanish) "It has produced error '9' in execution time. Subindex out of interval" .
I think that the line is:
With Sheets("sheet1")
Can you fix it? I really thank you anyway

I assume then you are not on sheet1 - change it to whatever you renamed the worksheet.

sheets("YOURSHEETNAME)