Hi all,

I have a question which is quite urgent indeed.

now I have a column with random numbers of arbitrary length. Given a
number, how can I find an UNIQUE combination of those numbers?

Example:
Suppose i have a list [1, 2, 3, 5], now
I want to know the combination of summation of 4 is 1 + 3.

How can I do that by VBA code?

Thanks for all.


---
Message posted from http://www.ExcelForum.com/

One method using solver:
http://groups.google.com/groups?thre...gp13.phx .gbl

will find a single solution.

The below will find multiple solutions if they exist. As Harlan points out
in the above thread the problem can quickly escalate to be unsolvable, but
for the numbers you show, it is solvable.

http://groups.google.com/groups?thre....supernews.com

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

"kaon " wrote in message
...
Hi all,

I have a question which is quite urgent indeed.

now I have a column with random numbers of arbitrary length. Given a
number, how can I find an UNIQUE combination of those numbers?

Example:
Suppose i have a list [1, 2, 3, 5], now
I want to know the combination of summation of 4 is 1 + 3.

How can I do that by VBA code?

Thanks for all.


---
Message posted from http://www.ExcelForum.com/

Great thanks, Tom!

Nice thread to share. If anyone knows how it works, better post her
and share with the readers :)

--
Message posted from http://www.ExcelForum.com

Does this vba support number with decimal points?

Seems it only works with integer.

Thanks

--
Message posted from http://www.ExcelForum.com

There is no reason it would not work with decimals except for the inherent
limitation of decimals - that they are not stored exactly.

The code uses an equality test. Generally this isn't suitable for decimal
numbers because sums of decimal numbers rarely pass such a test. You would
have to put in a limit such as if it is less than half a cent difference, it
is considered to be a match. The code could be modified to use such a test.

--
Regards,
Tom Ogilvy

"kaon " wrote in message
...
Does this vba support number with decimal points?

Seems it only works with integer.

Thanks!


---
Message posted from http://www.ExcelForum.com/

Hi Tom:

I tried the code you provided and it works well when my target number
is positive.

When I tried it with a target number that is negative, I am getting a
debug error "run time error 6" overflow and the line:
num = 2 ^ rng.Count - 1 is highlighted,
can you please tell me what might be wrong.

Thanks a lot.
Opekemi


"Tom Ogilvy" wrote in message ...
One method using solver:
http://groups.google.com/groups?thre...gp13.phx .gbl

will find a single solution.

The below will find multiple solutions if they exist. As Harlan points out
in the above thread the problem can quickly escalate to be unsolvable, but
for the numbers you show, it is solvable.

http://groups.google.com/groups?thre....supernews.com

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

"kaon " wrote in message
...
Hi all,

I have a question which is quite urgent indeed.

now I have a column with random numbers of arbitrary length. Given a
number, how can I find an UNIQUE combination of those numbers?

Example:
Suppose i have a list [1, 2, 3, 5], now
I want to know the combination of summation of 4 is 1 + 3.

How can I do that by VBA code?

Thanks for all.


---
Message posted from http://www.ExcelForum.com/

It won't work with negative numbers, but then a negative number doesn't make
sense in the context of the problem it was written for.

--
Regards,
Tom Ogilvy

"Kemi" wrote in message
m...
Hi Tom:

I tried the code you provided and it works well when my target number
is positive.

When I tried it with a target number that is negative, I am getting a
debug error "run time error 6" overflow and the line:
num = 2 ^ rng.Count - 1 is highlighted,
can you please tell me what might be wrong.

Thanks a lot.
Opekemi


"Tom Ogilvy" wrote in message
...
One method using solver:

http://groups.google.com/groups?thre...gp13.phx .gbl

will find a single solution.

The below will find multiple solutions if they exist. As Harlan points
out
in the above thread the problem can quickly escalate to be unsolvable,
but
for the numbers you show, it is solvable.


http://groups.google.com/groups?thre....supernews.com

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

"kaon " wrote in message
...
Hi all,

I have a question which is quite urgent indeed.

now I have a column with random numbers of arbitrary length. Given a
number, how can I find an UNIQUE combination of those numbers?

Example:
Suppose i have a list [1, 2, 3, 5], now
I want to know the combination of summation of 4 is 1 + 3.

How can I do that by VBA code?

Thanks for all.


---
Message posted from http://www.ExcelForum.com/

Hi Tom,

Would you know any similar code that would work for a target negative
number when I have a column of numbers that are both negative and
positive.

Thanks so much.
"Tom Ogilvy" wrote in message ...
It won't work with negative numbers, but then a negative number doesn't make
sense in the context of the problem it was written for.

--
Regards,
Tom Ogilvy

"Kemi" wrote in message
m...
Hi Tom:

I tried the code you provided and it works well when my target number
is positive.

When I tried it with a target number that is negative, I am getting a
debug error "run time error 6" overflow and the line:
num = 2 ^ rng.Count - 1 is highlighted,
can you please tell me what might be wrong.

Thanks a lot.
Opekemi


"Tom Ogilvy" wrote in message
...
One method using solver:

http://groups.google.com/groups?thre...gp13.phx .gbl

will find a single solution.

The below will find multiple solutions if they exist. As Harlan points
out
in the above thread the problem can quickly escalate to be unsolvable,
but
for the numbers you show, it is solvable.


http://groups.google.com/groups?thre....supernews.com

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

"kaon " wrote in message
...
Hi all,

I have a question which is quite urgent indeed.

now I have a column with random numbers of arbitrary length. Given a
number, how can I find an UNIQUE combination of those numbers?

Example:
Suppose i have a list [1, 2, 3, 5], now
I want to know the combination of summation of 4 is 1 + 3.

How can I do that by VBA code?

Thanks for all.


---
Message posted from http://www.ExcelForum.com/

I misunderstood what you were saying. It works fine when the target cell
(A1) is negative (of course some of your numbers in B must be negative). I
suspect you have too many numbers in column B.

--
Regards,
Tom Ogilvy

"Kemi" wrote in message
om...
Hi Tom,

Would you know any similar code that would work for a target negative
number when I have a column of numbers that are both negative and
positive.

Thanks so much.
"Tom Ogilvy" wrote in message
...
It won't work with negative numbers, but then a negative number doesn't
make
sense in the context of the problem it was written for.

--
Regards,
Tom Ogilvy

"Kemi" wrote in message
m...
Hi Tom:

I tried the code you provided and it works well when my target number
is positive.

When I tried it with a target number that is negative, I am getting a
debug error "run time error 6" overflow and the line:
num = 2 ^ rng.Count - 1 is highlighted,
can you please tell me what might be wrong.

Thanks a lot.
Opekemi


"Tom Ogilvy" wrote in message
...
One method using solver:


http://groups.google.com/groups?thre...gp13.phx .gbl

will find a single solution.

The below will find multiple solutions if they exist. As Harlan
points
out
in the above thread the problem can quickly escalate to be
unsolvable,
but
for the numbers you show, it is solvable.



http://groups.google.com/groups?thre....supernews.com

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

"kaon " wrote in message
...
Hi all,

I have a question which is quite urgent indeed.

now I have a column with random numbers of arbitrary length. Given
a
number, how can I find an UNIQUE combination of those numbers?

Example:
Suppose i have a list [1, 2, 3, 5], now
I want to know the combination of summation of 4 is 1 + 3.

How can I do that by VBA code?

Thanks for all.


---
Message posted from http://www.ExcelForum.com/

Tom

Is it possible to use this method but to allow multiple uses of th
numbers in the list to achieve a defined total?

Cheers

Arro

--
Message posted from http://www.ExcelForum.com

If you mean use the number multiple times but only list it once (10 + 10 +
10 = 30, but 10 is only listed once) then not with the solver approach.

--
Regards,
Tom Ogilvy

"arron laing " wrote in message
...
Tom

Is it possible to use this method but to allow multiple uses of the
numbers in the list to achieve a defined total?

Cheers

Arron


---
Message posted from http://www.ExcelForum.com/

The technique to do that using Solver is to use "Integer" constraints
instead of "Binary" constraints. Also, select the option "Assume
non-negative. However, it is often necessary to add the constraint that n
=0 in addition to n being "Integer." If you use an Integer constraint on a
specific number, then it usually helps Solver to remove any duplicates of
that number from your list.

HTH
Dana DeLouis


"Tom Ogilvy" wrote in message
...
If you mean use the number multiple times but only list it once (10 + 10
+
10 = 30, but 10 is only listed once) then not with the solver approach.

--
Regards,
Tom Ogilvy

"arron laing " wrote in
message
...
Tom

Is it possible to use this method but to allow multiple uses of the
numbers in the list to achieve a defined total?

Cheers

Arron