COMBINAISON

  • Thread starter Thread starter ...Patrick
  • Start date Start date
P

...Patrick

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, (e-mail address removed) 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.
 
Thank you Myrna !!!
I try this

....Patrick

Myrna Larson said:
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, (e-mail address removed) 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.
Click to expand...
 
You only wanted one solution?
--
Regards,
Tom Ogilvy

....Patrick said:
Thank you Myrna !!!
I try this

...Patrick

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

On Fri, 10 Nov 2000 16:19:53 GMT, (e-mail address removed) 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.
Click to expand...
Click to expand...
 
heu..........no if you have one :))
Thank you Tom
Patrick



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

...Patrick said:
Thank you Myrna !!!
I try this

...Patrick

Myrna Larson said:
Here's a message from 3 years ago that shows how to do this with Solver.
Click to expand...
I modified it a
bit for
Click to expand...
Click to expand...
 
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.

heu..........no if you have one :))
Thank you Tom
Patrick
Click to expand...
 
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 said:
heu..........no if you have one :))
Thank you Tom
Patrick
Click to expand...
 
Back
Top