combination of numbers

[kaon]

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.

 

[Tom Ogilvy]

One method using solver:
http://groups.google.com/[email protected]

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/[email protected]

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

 

[kaon]

Great thanks, Tom!

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

 

[kaon]

Does this vba support number with decimal points?

Seems it only works with integer.

Thanks

 

[Tom Ogilvy]

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.

 

[arron laing]

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

 

[Tom Ogilvy]

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.

 

[Dana DeLouis]

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

 

[Kemi]

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

One method using solver:
http://groups.google.com/[email protected]

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/[email protected]

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

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.

 

[Tom Ogilvy]

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

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" <[email protected]> wrote in message

One method using solver:
http://groups.google.com/[email protected]

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/[email protected]

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

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.

 

[Kemi]

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.

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

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" <[email protected]> wrote in message

One method using solver:
http://groups.google.com/[email protected]

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/[email protected]

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

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.

 

[Tom Ogilvy]

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

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" <[email protected]> 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

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" <[email protected]> wrote in message

One method using solver:

http://groups.google.com/[email protected]

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/[email protected]

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

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.