Macro to create all possible unique combinations of groups of numbers

[trpltongue]

Hello all,

I've been reading and working like crazy trying to make a silly thing work in excel vba and am at a roadblock.

I have a set of 30 numbers 1-30 that I want to place into 3 groups of 5, for example:

Group 1 - 1,2,3,4,5
Group 2 - 6,7,8,9,10
Group 3 - 11,12,13,14,15

However, each number can be used only once, so I cannot have for example:
Group 1 - 1,2,3,4,5
Group 2 - 5,4,3,2,1
Group 3 - 6,7,8,9,10

I'd like to use vba to generate all of the unique families of 3 groups of 5 numbers automatically.

So something like:

Family 1:
Group 1 - 1,2,3,4,5
Group 2 - 6,7,8,9,10
Group 3 - 11,12,13,14,15

Family 2:
Group 1 - 1,2,3,4,5
Group 2 - 6,7,8,9,10
Group 3 - 11,12,13,14,16

Family 3:
Group 1 - 1,2,3,4,5
Group 2 - 6,7,8,9,10
Group 3 - 11,12,13,14,17

Etc.

I've read all through Myrna Larson's code and have no problem generating all of the possible 5 number combinations, it's just putting them into unique 3-group combinations that is troublesome.

Thanks so much for any help.

 

[pgc01]

Hi

If I understand correctly you just have to generate the combinations of 30 number taken 15 at a time. Then out of each combination you make 3 groups of 5 elements that will be your 3 groups.

This said, I think it makes no sense to generate 115 million possibilites.
Also how would you store it? Since you have 3 groups for each combination you'd need 445 columns with 1 million groups each.

I can see no use for something like this.

Please comment.

 

[trpltongue]

Hi

If I understand correctly you just have to generate the combinations of 30 number taken 15 at a time. Then out of each combination you make 3 groups of 5 elements that will be your 3 groups.

This said, I think it makes no sense to generate 115 million possibilites.
Also how would you store it? Since you have 3 groups for each combination you'd need 445 columns with 1 million groups each.

I can see no use for something like this.

Please comment.

I must admit I hadn't thought about the absolute number of possible combinations. If there are 115 million possibilities, then there really is no point in the exercise.

Thanks for bringing me back to reality

 

[pgc01]

I'm glad it helped.

It's common for this type of problems to deal of big number of possibilities. In that case sometimes you can define a set of restrictions that makes it more feasible.

(and 3 times 115 is 345 and not 445, like I wrote )

 

[trpltongue]

Okay, so I don't want to give up quite so easily yet

How about on a smaller scale. Say 2 groups of 3 numbers, from a total of 10 numbers.

Getting the unique 6 number combinations is no problem, but how then do I go about automatically making the possible groups of 3 numbers?


For instance

2 groups - 1,2,3 and 4,5,6 would be different than
2 groups - 2,3,4 and 1,5,6

Thanks!

 

[pgc01]

Okay, so I don't want to give up quite so easily yet

How about on a smaller scale. Say 2 groups of 3 numbers, from a total of 10 numbers.

Getting the unique 6 number combinations is no problem, but how then do I go about automatically making the possible groups of 3 numbers?


For instance

2 groups - 1,2,3 and 4,5,6 would be different than
2 groups - 2,3,4 and 1,5,6

Thanks!

Hi

In that case it's different.

There will be more possible results also. If the 2 groups you post would be considered as equivalent the number of possibilities would be

=COMBIN(10,6)

which is equal to 210. In the case they are not equivalent, like you wrote, the number of possibilities would be

=MULTINOMIAL(3,3,4)

which is 4200. For each of the combinations for the first group (120) you'll have to generate the combinations for the second group (35), which gives 120*35=4200.

You'll have to find code that generates all the possibilites for the multinomial distribution or you'll have to adapt the code for the combinations to make it into 2 steps. First generate the combinations for the first group, and then generate the combinations for the second group using only the elements not used in the first group.

If you need help I can try it this night (GMT).

In that case, to make sure, confirm that the order of the elements inside a group is not relevant, meaning, for ex.:

1234 5678
2314 5687

is equivalent.

 

[paddydive]

hi trpltongue,

Try this...

Sub UniqueRandomNumbers()
Dim Cl As Range, Rng As Range

Set Rng = Range("A1").Resize(3, 5)

For Each Cl In Rng
RNo = 1 + Int(Rnd * 30)
RepNo = Application.WorksheetFunction.CountIf(Rng, RNo)

Do Until RepNo = 0
RNo = 1 + Int(Rnd * Rng.Count)
RepNo = Application.WorksheetFunction.CountIf(Rng, RNo)
Loop

Cl.Value = RNo
Next
End Sub

 

[trpltongue]

PGC,

Yes, you are correct that the 2 groups are not considered equivalent so there would be in fact more combinations.

Sorry for the short reply, but I'm running out just now.

paddydive,

I did try the code you posted but I'm not sure I follow what's going on. I tried to run it and excel hung up on me?

 

[pgc01]

So, in conclusion
- you have a set with 10 elements
- you want a list of 2 groups of 3 elements
- the elements are not repeated, neither inside one of the groups nor across the groups
- the order of the elements in each of the groups is relevant

If this is the case it's very simple again. Instead of generating a list with the combinations you generate a list of the permutations of 10 elements taken 6 at a time. In each of the permutations take the first 3 elements, that's your first group, and the next 3 elements, that's your second group.

There are lots of places whrere you can find code to generate permutations, for ex. here in this board.

If you have problems with it post back.

In this case the total number of permutations is:

=PERMUT(10,6)

equal to 151,200.

 

[paddydive]

PGC,

paddydive,

I did try the code you posted but I'm not sure I follow what's going on. I tried to run it and excel hung up on me?

hi trpltongue,

Code is working fine on my computer... its a small for..next code and it shouldn't hung up... try running code line by line.. this will give u set of 3x5 number from 1 to 30 and with no repeated number... hope it will work for u.