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.
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" <[email protected]> wrote in message news:[email protected]...
>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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks