COMBINAISON

...Patrick

unread,

Nov 7, 2003, 4:19:28 PM11/7/03

to

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

Myrna Larson

unread,

Nov 7, 2003, 9:20:04 PM11/7/03

to

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, m_r_be...@my-deja.com 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 are:
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.

...Patrick

unread,

Nov 8, 2003, 3:35:58 PM11/8/03

to

Thank you Myrna !!!
I try this

...Patrick

"Myrna Larson" <myrnai...@chartermi.net> a écrit dans le message de
news:e8koqv4lt7q82rjoe...@4ax.com...

Tom Ogilvy

unread,

Nov 8, 2003, 6:26:30 PM11/8/03

to

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

...Patrick <NOspamp...@hotmail.com> wrote in message
news:O5ZEvfjp...@tk2msftngp13.phx.gbl...

...Patrick

unread,

Nov 9, 2003, 1:55:45 PM11/9/03

to

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

"Tom Ogilvy" <twog...@msn.com> a écrit dans le message de
news:vqquutf...@news.supernews.com...

Myrna Larson

unread,

Nov 9, 2003, 5:37:14 PM11/9/03

to

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.

Tom Ogilvy

unread,

Nov 9, 2003, 11:18:28 PM11/9/03

to

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 <NOspamp...@hotmail.com> wrote in message

news:upseZMvp...@TK2MSFTNGP11.phx.gbl...

...Patrick

unread,

Nov 10, 2003, 6:31:11 AM11/10/03

to

thanks a lot Tom !!!