Generating multiple variable permutations

[rblaustein]

Now here's a tricky one!!!...
I'm trying to write some VBA code to generate a sequence of
combinations of numbers and letters. A single letter will always be
combined with a single number, but a letter will never be combined with
another letter or a number will never be combined with another number
(i.e.- combining unlike variable types in pairs, for example: 1A, 2A,
1B, 2B). Numbers always preceed letters in each pair. Also, the
unlike pair combinations should be grouped for all possible unlike pair
combinations (total possible unlike pair
combinations=QtyVarTypeLetter*QtyVarTypeNumber). For example, if
variable type Letter has two possibilities (A and B), and variable type
Number also has two possibilities (1 and 2), then there would be 4 pair
combinations in each group (e.g.- 1A, 2A, 1B, 2B).

Additionally, like number pairs should be kept together (i.e.- not
alternated) in each group. Now my question becomes, how do I generate
all the possible permutations of the above described groups? In the
example I've been describing there would be y permutations (I'm pretty
sure that y=Letters!^Numbers*Numbers!=2^2*2=8). The permutations would
be the following:

1A1B 2A2B
1B1A 2A2B
1A1B 2B2A
1B1A 2B2A
2A2B 1A1B
2B2A 1A1B
2A2B 1B1A
2B2A 1B1A

Conceptually speaking, one thing to liken this to is rings on cones.
If you have 2 blue rings and 2 red rings and two movable cones (1 and
2). Then what are all the possible combinations of rings on cones,
with only ring color order and cone order being important, and needing
to have exactly 2 different color rings on each cone (one ring must be
red and one ring must be blue on each cone)?

Thanks for your help on this!!! I know it isn't an easy one!!!

 

[Tom Ogilvy]

http://www.j-walk.com/ss/excel/tips/tip46.htm

use this as a "generator" to make your permutations.

 

[rblaustein]

Tom-
I am familiar with the piece of code that you directed me to, however,
this will not do what I described in my original post. While this is
an impressive piece of code for generating simple permuations, the
situation I am looking at is much more complex.