Hi ! and sorry for my poor english....
I have a number like 100 in A1 and a little list of numbers in B1:Bxx and i would like to
find what numbers in B make a total equal to A1

ex:in B1:Bxx
50
2
6
44
12
9
17
23
20
45
40

in this case i have : 50+6+44 = 100 or 17+23+20+40= 100

Is it possible ?

Thanks a lot

....Patrick

Here's a message from 3 years ago that shows how to do this with Solver. I modified it a bit for
your problem.

On Fri, 10 Nov 2000 16:19:53 GMT, wrote:

Problem: You have numbers in A1:A5. You want to find a combination of those
numbers whose sum is 100.

In B1:B5, put the number 1.

In B6, put the formula =SUMPRODUCT(A1:A5,B1:B5).

In B7, put the formula =COUNTIF(B1:B5,1)


Then use Solver.

The Target cell is B6
The goal is that B6 has a value of 100
The cells to change are B1:B5
The constraints a
B1:B5 must be "bin" (i.e. binary -- 1 or 0 -- which wasn't obvious to me)
B7 must be = 1


Note that this will only find one combination; there may be more than one.

On Fri, 7 Nov 2003 22:19:28 +0100, "...Patrick" wrote:

Hi ! and sorry for my poor english....
I have a number like 100 in A1 and a little list of numbers in B1:Bxx and i would like to
find what numbers in B make a total equal to A1

ex:in B1:Bxx
50
2
6
44
12
9
17
23
20
45
40

in this case i have : 50+6+44 = 100 or 17+23+20+40= 100

Is it possible ?

Thanks a lot

...Patrick

Thank you Myrna !!!
I try this

....Patrick

"Myrna Larson" a écrit dans le message de
...
Here's a message from 3 years ago that shows how to do this with Solver. I modified it a
bit for
your problem.

On Fri, 10 Nov 2000 16:19:53 GMT, wrote:

Problem: You have numbers in A1:A5. You want to find a combination of those
numbers whose sum is 100.

In B1:B5, put the number 1.

In B6, put the formula =SUMPRODUCT(A1:A5,B1:B5).

In B7, put the formula =COUNTIF(B1:B5,1)


Then use Solver.

The Target cell is B6
The goal is that B6 has a value of 100
The cells to change are B1:B5
The constraints a
B1:B5 must be "bin" (i.e. binary -- 1 or 0 -- which wasn't obvious to me)
B7 must be = 1


Note that this will only find one combination; there may be more than one.

On Fri, 7 Nov 2003 22:19:28 +0100, "...Patrick" wrote:

Hi ! and sorry for my poor english....
I have a number like 100 in A1 and a little list of numbers in B1:Bxx and i would like
to
find what numbers in B make a total equal to A1

ex:in B1:Bxx
50
2
6
44
12
9
17
23
20
45
40

in this case i have : 50+6+44 = 100 or 17+23+20+40= 100

Is it possible ?

Thanks a lot

...Patrick

You only wanted one solution?
--
Regards,
Tom Ogilvy

....Patrick wrote in message
...
Thank you Myrna !!!
I try this

...Patrick

"Myrna Larson" a écrit dans le message de
...
Here's a message from 3 years ago that shows how to do this with Solver.
I modified it a
bit for
your problem.

On Fri, 10 Nov 2000 16:19:53 GMT, wrote:

Problem: You have numbers in A1:A5. You want to find a combination of
those
numbers whose sum is 100.

In B1:B5, put the number 1.

In B6, put the formula =SUMPRODUCT(A1:A5,B1:B5).

In B7, put the formula =COUNTIF(B1:B5,1)


Then use Solver.

The Target cell is B6
The goal is that B6 has a value of 100
The cells to change are B1:B5
The constraints a
B1:B5 must be "bin" (i.e. binary -- 1 or 0 -- which wasn't obvious to
me)
B7 must be = 1


Note that this will only find one combination; there may be more than
one.

On Fri, 7 Nov 2003 22:19:28 +0100, "...Patrick"
wrote:

Hi ! and sorry for my poor english....
I have a number like 100 in A1 and a little list of numbers in B1:Bxx
and i would like
to
find what numbers in B make a total equal to A1

ex:in B1:Bxx
50
2
6
44
12
9
17
23
20
45
40

in this case i have : 50+6+44 = 100 or 17+23+20+40= 100

Is it possible ?

Thanks a lot

...Patrick

heu..........no if you have one :))
Thank you Tom
Patrick



"Tom Ogilvy" a écrit dans le message de
...
You only wanted one solution?
--
Regards,
Tom Ogilvy

...Patrick wrote in message
...
Thank you Myrna !!!
I try this

...Patrick

"Myrna Larson" a écrit dans le message de
...
Here's a message from 3 years ago that shows how to do this with Solver.
I modified it a
bit for
your problem.

On Fri, 10 Nov 2000 16:19:53 GMT, wrote:

Problem: You have numbers in A1:A5. You want to find a combination of
those
numbers whose sum is 100.

In B1:B5, put the number 1.

In B6, put the formula =SUMPRODUCT(A1:A5,B1:B5).

In B7, put the formula =COUNTIF(B1:B5,1)


Then use Solver.

The Target cell is B6
The goal is that B6 has a value of 100
The cells to change are B1:B5
The constraints a
B1:B5 must be "bin" (i.e. binary -- 1 or 0 -- which wasn't obvious to
me)
B7 must be = 1


Note that this will only find one combination; there may be more than
one.

On Fri, 7 Nov 2003 22:19:28 +0100, "...Patrick"
wrote:

Hi ! and sorry for my poor english....
I have a number like 100 in A1 and a little list of numbers in B1:Bxx
and i would like
to
find what numbers in B make a total equal to A1

ex:in B1:Bxx
50
2
6
44
12
9
17
23
20
45
40

in this case i have : 50+6+44 = 100 or 17+23+20+40= 100

Is it possible ?

Thanks a lot

...Patrick

To find all solutions would require a macro, and if your list is more than a few rows, it would
probably be too slow (i.e. taking several minutes to solve) to be practical.

On Sun, 9 Nov 2003 19:55:45 +0100, "...Patrick" wrote:

heu..........no if you have one :))
Thank you Tom
Patrick



"Tom Ogilvy" a écrit dans le message de
...
You only wanted one solution?
--
Regards,
Tom Ogilvy

...Patrick wrote in message
...
Thank you Myrna !!!
I try this

...Patrick

"Myrna Larson" a écrit dans le message de
...
Here's a message from 3 years ago that shows how to do this with Solver.
I modified it a
bit for
your problem.

On Fri, 10 Nov 2000 16:19:53 GMT, wrote:

Problem: You have numbers in A1:A5. You want to find a combination of
those
numbers whose sum is 100.

In B1:B5, put the number 1.

In B6, put the formula =SUMPRODUCT(A1:A5,B1:B5).

In B7, put the formula =COUNTIF(B1:B5,1)


Then use Solver.

The Target cell is B6
The goal is that B6 has a value of 100
The cells to change are B1:B5
The constraints a
B1:B5 must be "bin" (i.e. binary -- 1 or 0 -- which wasn't obvious to
me)
B7 must be = 1


Note that this will only find one combination; there may be more than
one.

On Fri, 7 Nov 2003 22:19:28 +0100, "...Patrick"
wrote:

Hi ! and sorry for my poor english....
I have a number like 100 in A1 and a little list of numbers in B1:Bxx
and i would like
to
find what numbers in B make a total equal to A1

ex:in B1:Bxx
50
2
6
44
12
9
17
23
20
45
40

in this case i have : 50+6+44 = 100 or 17+23+20+40= 100

Is it possible ?

Thanks a lot

...Patrick

Combinations
binary
VBA

with your sample data in B1:B11 this produces 11 solutions. Run Testbldbin
as written it is limited to about 254 solutions

Sub bldbin(num As Long, bits As Long, arr() As Long)
Dim lNum As Long, i 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
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 = 100 Then
icol = icol + 1
rng.Offset(0, icol) = varr1
if icol = 256 then
msgbox "too many columns, i is " & i & " of " & num & _
" combinations checked"
exit sub
End if
End If
Next
End Sub


--
Regards,
Tom Ogilvy


....Patrick wrote in message
...
heu..........no if you have one :))
Thank you Tom
Patrick



"Tom Ogilvy" a écrit dans le message de
...
You only wanted one solution?
--
Regards,
Tom Ogilvy

...Patrick wrote in message
...
Thank you Myrna !!!
I try this

...Patrick

"Myrna Larson" a écrit dans le message de
...
Here's a message from 3 years ago that shows how to do this with
Solver.
I modified it a
bit for
your problem.

On Fri, 10 Nov 2000 16:19:53 GMT, wrote:

Problem: You have numbers in A1:A5. You want to find a combination
of
those
numbers whose sum is 100.

In B1:B5, put the number 1.

In B6, put the formula =SUMPRODUCT(A1:A5,B1:B5).

In B7, put the formula =COUNTIF(B1:B5,1)


Then use Solver.

The Target cell is B6
The goal is that B6 has a value of 100
The cells to change are B1:B5
The constraints a
B1:B5 must be "bin" (i.e. binary -- 1 or 0 -- which wasn't
obvious to
me)
B7 must be = 1


Note that this will only find one combination; there may be more
than
one.

On Fri, 7 Nov 2003 22:19:28 +0100, "...Patrick"
wrote:

Hi ! and sorry for my poor english....
I have a number like 100 in A1 and a little list of numbers in
B1:Bxx
and i would like
to
find what numbers in B make a total equal to A1

ex:in B1:Bxx
50
2
6
44
12
9
17
23
20
45
40

in this case i have : 50+6+44 = 100 or 17+23+20+40= 100

Is it possible ?

Thanks a lot

...Patrick

thanks a lot Tom !!!