Hi, all
I want to return all possible combinations using the numbers of a specified
range.
For example, if I specify a certain range, whose value is {1,2;3,4}, and
input 2, the hopeful result is {1,2;1,3;1,4;2,3;2,4;3,4}.
What I want is sub procedure with two arguments(range, number of selection).

I know how can do this only if the number of selection is fixed.

Sub AllCombi()
Dim rngX As Range
Dim intX As Integer
Dim intY As Integer
Dim intZ As Integer
Dim intA As Integer
Set rngX = Application.InputBox("Specify the range", Type:=8)
intX = rngX.Cells.Count
intA = 1
For intY = 1 To intX - 1
For intZ = intY + 1 To intX
Cells(intA, 1) = intY
Cells(intA, 2) = intZ
intA = intA + 1
Next intZ
Next intY
End Sub

When the number of looping sentence is a variable, how can I solve it?
That's the point.

Any advice would be much appreciated.

Use Google and search the Excel newsgroups for messages from Myrna Larson
containing key words combination and permutation. The code to accommodate a
variable set size is not trivial. The procedure is a SUB, not a FUNCTION.

You can probably modify it to turn it into a function that returns only
combinations. The problem with a function is that if would have to be an array
function. As such, you would have to select the correct number of cells when
you enter the formula; the number of combinations generally not known at that
point, though you could get it from the COMBIN formula.


On Fri, 22 Oct 2004 05:39:51 +0900, "Seokho Moon" wrote:

Hi, all
I want to return all possible combinations using the numbers of a specified
range.
For example, if I specify a certain range, whose value is {1,2;3,4}, and
input 2, the hopeful result is {1,2;1,3;1,4;2,3;2,4;3,4}.
What I want is sub procedure with two arguments(range, number of selection).

I know how can do this only if the number of selection is fixed.

Sub AllCombi()
Dim rngX As Range
Dim intX As Integer
Dim intY As Integer
Dim intZ As Integer
Dim intA As Integer
Set rngX = Application.InputBox("Specify the range", Type:=8)
intX = rngX.Cells.Count
intA = 1
For intY = 1 To intX - 1
For intZ = intY + 1 To intX
Cells(intA, 1) = intY
Cells(intA, 2) = intZ
intA = intA + 1
Next intZ
Next intY
End Sub

When the number of looping sentence is a variable, how can I solve it?
That's the point.

Any advice would be much appreciated.

Hi Myrna,

I would like making a permutation with your sub, but I have a big problem,
I'm not a professionnal on VBA with you and I don't know how to make a
modification. I explain:
I have this series: 1 1 1 1 0 0 0 0
I want to make a permutation, but I don't want a same series.
Example:
1 1 1 1 0 0 0 0
1 1 1 0 1 0 0 0
1 1 1 0 0 1 0 0 etc

I don't want:
1 1 1 1 0 0 0 0
1 1 1 1 0 0 0 0
1 1 1 1 0 0 0 0
1 1 1 1 0 0 0 0
1 1 1 1 0 0 0 0
.....

Thank you so much

Starwing

It's too late now (Amsterdam 03:00 at night)

i have some code in the works but need to finalize and tune tomorrow.

How large are the combinations you'll require?

is it ok to exit on a max of 65000 combinations ?
or should it continue until memory is maxed out?
or should it write to an external file?

as you may realize the quantities can be stupendous..
...

i'll be back :)


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Seokho Moon" wrote:

Hi, all
I want to return all possible combinations using the numbers of a
specified range.
For example, if I specify a certain range, whose value is {1,2;3,4},
and input 2, the hopeful result is {1,2;1,3;1,4;2,3;2,4;3,4}.
What I want is sub procedure with two arguments(range, number of
selection).

I know how can do this only if the number of selection is fixed.

Sub AllCombi()
Dim rngX As Range
Dim intX As Integer
Dim intY As Integer
Dim intZ As Integer
Dim intA As Integer
Set rngX = Application.InputBox("Specify the range", Type:=8)
intX = rngX.Cells.Count
intA = 1
For intY = 1 To intX - 1
For intZ = intY + 1 To intX
Cells(intA, 1) = intY
Cells(intA, 2) = intZ
intA = intA + 1
Next intZ
Next intY
End Sub

When the number of looping sentence is a variable, how can I solve it?
That's the point.

Any advice would be much appreciated.

Then use the code as is, and after it finishes, use Data/Filter/Advanced and
select Unique Records only. That will eliminate the duplicates.


On Thu, 21 Oct 2004 19:53:38 -0400, "Starwing" wrote:

Hi Myrna,

I would like making a permutation with your sub, but I have a big problem,
I'm not a professionnal on VBA with you and I don't know how to make a
modification. I explain:
I have this series: 1 1 1 1 0 0 0 0
I want to make a permutation, but I don't want a same series.
Example:
1 1 1 1 0 0 0 0
1 1 1 0 1 0 0 0
1 1 1 0 0 1 0 0 etc

I don't want:
1 1 1 1 0 0 0 0
1 1 1 1 0 0 0 0
1 1 1 1 0 0 0 0
1 1 1 1 0 0 0 0
1 1 1 1 0 0 0 0
....

Thank you so much

Starwing

Your code seems to be able to pick-up "only" the range (the numbers
involved) but not the "number of elements in combinations"?. Am I missing
something here?
TIA


"Seokho Moon" wrote in message
...
Hi, all
I want to return all possible combinations using the numbers of a
specified
range.
For example, if I specify a certain range, whose value is {1,2;3,4}, and
input 2, the hopeful result is {1,2;1,3;1,4;2,3;2,4;3,4}.
What I want is sub procedure with two arguments(range, number of
selection).

I know how can do this only if the number of selection is fixed.

Sub AllCombi()
Dim rngX As Range
Dim intX As Integer
Dim intY As Integer
Dim intZ As Integer
Dim intA As Integer
Set rngX = Application.InputBox("Specify the range", Type:=8)
intX = rngX.Cells.Count
intA = 1
For intY = 1 To intX - 1
For intZ = intY + 1 To intX
Cells(intA, 1) = intY
Cells(intA, 2) = intZ
intA = intA + 1
Next intZ
Next intY
End Sub

When the number of looping sentence is a variable, how can I solve it?
That's the point.

Any advice would be much appreciated.

Thank you Myrna,
It's a good solution ;-)

Starwing
"Myrna Larson" a écrit dans le message
de ...
Then use the code as is, and after it finishes, use Data/Filter/Advanced
and
select Unique Records only. That will eliminate the duplicates.


On Thu, 21 Oct 2004 19:53:38 -0400, "Starwing" wrote:

Hi Myrna,

I would like making a permutation with your sub, but I have a big
problem,
I'm not a professionnal on VBA with you and I don't know how to make a
modification. I explain:
I have this series: 1 1 1 1 0 0 0 0
I want to make a permutation, but I don't want a same series.
Example:
1 1 1 1 0 0 0 0
1 1 1 0 1 0 0 0
1 1 1 0 0 1 0 0 etc

I don't want:
1 1 1 1 0 0 0 0
1 1 1 1 0 0 0 0
1 1 1 1 0 0 0 0
1 1 1 1 0 0 0 0
1 1 1 1 0 0 0 0
....

Thank you so much

Starwing

You must be, but I don't know what. The code requires a specific layout: C or
P in the top cell, set size in the cell below, and items in the population in
the cells below that. If you set it up correctly, it works without problems.

On Fri, 22 Oct 2004 14:36:13 +0300, "J_J" wrote:

Your code seems to be able to pick-up "only" the range (the numbers
involved) but not the "number of elements in combinations"?. Am I missing
something here?
TIA


"Seokho Moon" wrote in message
...
Hi, all
I want to return all possible combinations using the numbers of a
specified
range.
For example, if I specify a certain range, whose value is {1,2;3,4}, and
input 2, the hopeful result is {1,2;1,3;1,4;2,3;2,4;3,4}.
What I want is sub procedure with two arguments(range, number of
selection).

I know how can do this only if the number of selection is fixed.

Sub AllCombi()
Dim rngX As Range
Dim intX As Integer
Dim intY As Integer
Dim intZ As Integer
Dim intA As Integer
Set rngX = Application.InputBox("Specify the range", Type:=8)
intX = rngX.Cells.Count
intA = 1
For intY = 1 To intX - 1
For intZ = intY + 1 To intX
Cells(intA, 1) = intY
Cells(intA, 2) = intZ
intA = intA + 1
Next intZ
Next intY
End Sub

When the number of looping sentence is a variable, how can I solve it?
That's the point.

Any advice would be much appreciated.