easy problem for programmers
 

As I'm no programmer I need help from you. I have a column
with a range of values (amounts). In a second column I
have only 1 amount(total). Now I want to know every
combination of amounts from the first row that added
together equals the total amount of the second row. It may
be a combination of 2, 3 or more values. The combinations
that match should be copied to a new column. I hope I made
myself clear. How can this be done ? Thanks.

easy problem for programmers
 

Put your numbers in Column B, starting in B1
Put the number to sum to in A1
Run TestBldBin

this will list all combinations in columns going to the right - obviously it
runs out of room at 254. If nothing is shown, there are no combinations
the more numbers you have in column B, the longer it will take to calculate.
I am sure there is some relatively small finite number of numbers where this
will blow up, but I haven't really given it much thought.


Option Explicit
Sub bldbin(num As Long, bits As Long, arr() As Long)
Dim lNum As Long, i As Long, cnt As Long
lNum = num
' Dim sStr As String
' sStr = ""
cnt = 0
For i = bits - 1 To 0 Step -1
If lNum And 2 ^ i Then
cnt = cnt + 1
arr(i, 0) = 1
' sStr = sStr & "1"
Else
arr(i, 0) = 0
' sStr = sStr & "0"
End If
Next
' If cnt = 2 Then
' Debug.Print num, sStr
' End If
End Sub

Sub TestBldbin()
Dim i As Long
Dim bits As Long
Dim varr As Variant
Dim varr1() As Long
Dim rng As Range
Dim icol As Long
Dim tot As Long
Dim num As Long
icol = 0
Set rng = Range(Range("B1"), Range("B1").End(xlDown))
num = 2 ^ rng.Count - 1
bits = rng.Count
varr = rng.Value
ReDim varr1(0 To bits - 1, 0 To 0)
For i = 0 To num
bldbin i, bits, varr1
tot = Application.SumProduct(varr, varr1)
If tot = Range("A1") Then
icol = icol + 1
If icol = 255 Then
MsgBox "too many columns, i is " & i & " of " & num & _
" combinations checked"
Exit Sub
End If
rng.Offset(0, icol) = varr1
End If
Next
End Sub

--
Regards,
Tom Ogilvy

"Freddy" wrote in message
...
As I'm no programmer I need help from you. I have a column
with a range of values (amounts). In a second column I
have only 1 amount(total). Now I want to know every
combination of amounts from the first row that added
together equals the total amount of the second row. It may
be a combination of 2, 3 or more values. The combinations
that match should be copied to a new column. I hope I made
myself clear. How can this be done ? Thanks.

easy problem for programmers
 

Freddy,

Contact me privately, and I will send you a workbook that does just what you
want.

HTH,
Bernie
MS Excel MVP

"Freddy" wrote in message
...
As I'm no programmer I need help from you. I have a column
with a range of values (amounts). In a second column I
have only 1 amount(total). Now I want to know every
combination of amounts from the first row that added
together equals the total amount of the second row. It may
be a combination of 2, 3 or more values. The combinations
that match should be copied to a new column. I hope I made
myself clear. How can this be done ? Thanks.