All combinations

[Seokho Moon]

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.

 

[Myrna Larson]

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.

 

[Starwing]

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

 

[keepITcool]

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 >

 

[Myrna Larson]

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

 

[J_J]

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

 

[Starwing]

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

Starwing

 

[Myrna Larson]

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.