which numbers make up a given total

[kchew]

I have sets of about 12 numbers. I need to know which few numbers add up to a certain specific total. Can Excel do this .. any resource is greatly appreciated

[Chris Davison]

Excel can do everything

I have a quick n easy file that does this which I can email you.... drop me a line, or reply with your email

novulari@hotmail.com

(normally, I'd post the VBA code, but there are a few instructions that don't appear which are in my file)

Chris

[Chris Davison]

Actually, here's the code I use, if anyone recognises it please claim it so I can thank you !


Sub SumCertain()
Dim a(100) As Double
Dim Targt As Double
Targt = InputBox("Enter Target")
Sol1 = ""
n = 0
For Each cell In Selection
n = n + 1
a(n) = cell.Value
If Abs(a(n) - Targt) < 0.01 Then Sol1 = Sol1 & a(n) & Chr(10)
Next

MsgBox Sol1, vbOKOnly, "Solutions with 1 Variable"

Sol2 = ""
For r = 1 To n
For s = 1 To n
If r = s Then GoTo nxt2
If a(r) + a(s) = Targt Then Sol2 = Sol2 & a(r) & "+" & a(s) & Chr(10)
nxt2:
Next
Next

MsgBox Sol2, vbOKOnly, "Solutions with 2 Variables"

Sol3 = ""
For r = 1 To n
For s = 1 To n
For t = 1 To n
If r = s Then GoTo nxt3
If r = t Then GoTo nxt3
If s = t Then GoTo nxt3
If a(r) + a(s) + a(t) = Targt Then Sol3 = Sol3 & a(r) & "+" & a(s) & "+" & a(t) & Chr(10)
nxt3:
Next
Next
Next

MsgBox Sol3, vbOKOnly, "Solutions with 3 Variables"

Sol4 = ""
For r = 1 To n
For s = 1 To n
For t = 1 To n
For u = 1 To n
If r = s Then GoTo nxt4
If r = t Then GoTo nxt4
If r = u Then GoTo nxt4
If s = t Then GoTo nxt4
If s = u Then GoTo nxt4
If t = u Then GoTo nxt4
If a(r) + a(s) + a(t) + a(u) = Targt Then Sol4 = Sol4 & a(r) & "+" & a(s) & "+" & a(t) & "+" & a(u) & Chr(10)
nxt4:
Next
Next
Next
Next

MsgBox Sol4, vbOKOnly, "Solutions with 4 Variables"

Sol5 = ""
For r = 1 To n
For s = 1 To n
For t = 1 To n
For u = 1 To n
For v = 1 To n
If r = s Then GoTo nxt5
If r = t Then GoTo nxt5
If r = u Then GoTo nxt5
If r = v Then GoTo nxt5
If s = t Then GoTo nxt5
If s = u Then GoTo nxt5
If s = v Then GoTo nxt5
If t = u Then GoTo nxt5
If t = v Then GoTo nxt5
If u = v Then GoTo nxt5
If a(r) + a(s) + a(t) + a(u) + a(v) = Targt Then Sol5 = Sol5 & a(r) & "+" & a(s) & "+" & a(t) & "+" & a(u) & "+" & a(v) & Chr(10)
nxt5:
Next
Next
Next
Next
Next

MsgBox Sol5, vbOKOnly, "Solutions with 5 Variables"

End Sub

[Chris Davison]

Poster needs to check that up to any 12 numbers form a total....

my code takes care of any 5 only - I haven't bothered to augment it to look for more as the possible purmutations searched via this method runs into gazillions and takes too long....

can anyone else help with some more efficient VBA ?

ta
Chris