Total a Combination of Numbers
 

I have a list of 19 numbers, what I need to know is what combination of these
add up to 690. I'm drawing a blank on how I could set this up in Excel.

This is probably best done in a macro

To make this example work...

* Put your 19 Values in G1 to G19
* Open VB & add & run the following macro

Option Explicit
Public Sub WhichNosAddTo690()
Dim i As Variant
Dim CurrentCell As Range
Dim StartCell As Range
Dim OutputCell As Range
Dim iTest As Long, sOut As String
Dim bitno As Integer
'Set starting cells for input & output
Set StartCell = Range("G1")
Set OutputCell = Range("H1")


For i = 0 To (2 ^ 19)
iTest = 0
For bitno = 0 To 18 '19 bits
If ((i And (2 ^ bitno)) 0) Then
iTest = iTest + StartCell.Offset(bitno).value
End If
Next
If iTest = 690 Then
sOut = ""
For bitno = 0 To 18 '19 bits
If ((i And (2 ^ bitno)) 0) Then
sOut = sOut & " " & StartCell.Offset(bitno).value
End If
Next
OutputCell.value = Trim(sOut)
Set OutputCell = OutputCell.Offset(1, 0)
End If
Next

End Sub

WHAT IT DOES:
* Add all possible combinations of values in cells G1 to G19.
* Outputs a space separated list of all the combinations that add up to 690 in Col H

IMPORTANT NOTE:
* It will take a while for this function to complete, be patient.

HOW IT WORKS...
'Loop all possible values upto 2^19
'Use the bit pattern of loop 'i' to
'test all the possible combinations
'e.g. when i = 9, a 19 bit - bit pattern would look like
' 0000000000000001001
'Based on this bit pattern, test the sum of
'G1+G4
'when i = 22, the 19 bit - bit pattern would look like
' 0000000000000010110
'Based on this bit pattern, test the sum of
'G2+G3+G5
'etc etc

"Erika" wrote in message ...
I have a list of 19 numbers, what I need to know is what combination of these
add up to 690. I'm drawing a blank on how I could set this up in Excel.