Help generating combinations

[JamRock]

Hi Group,
I have some text data in a range of cells. Example below

QuesType ANSWER
Tele A
Tele B
Tele C
Comm D
Comm E
Satelite F
Satelite G
Satelite H
Satelite I
Satelite J

I want to write VBA code that will generate all the question
type/answer combinations.
The code must count the number of question type and for each question
type,
the number of different possible answer. It should then generate the
question
types and answer as show below - in this case 30 combinations - and
write
this out to a new worksheet.

This needs to be dynamic so that a user could for example enter a new
question
type with a set of possible answers or a new/additional answers for a
question type
category. He would then execute the code to regenerate a new set of
question type /answer combinations. I have looked at
combination/permutaion generating code posted her
before but I have been unable to adapt any of it to this specific
situation.

Any help will be appreciated.


Tele Comm Satelite
A D F
A E F
B D F
B E F
C D F
C E F
A D G
A E G
B D G
B E G
C D G
C E G
A D H
A E H
B D H
B E H
C D H
C E H
A D I
A E I
B D I
B E I
C D I
C E I
A D J
A E J
B D J
B E J
C D J
C E J
Thanks

 

[JamRock]

Hi Group
Any help will be appreciated, even an indication of the best place to
post the question.

Thanks and regards

 

[Tom Ogilvy]

I suspect most don't see the utility in enumerating combinations. Anyway,

for your sample problem you would pick up the possibilities for each
category in an array

since you want to write the code, here is the basic structure

Dim a, b, c, i, j, k
Dim sh as Worksheet, rw as long
Dim sh1 as Worksheet
set sh1 = ActiveSheet
rw = 1
a = Range("B2:B4").value
b = Range("B5:B6").Value
c = Range("B7:B11").Value
set sh = Worksheets.Add
sh1.Activate
for i = 1 to ubound(a,1)
for j = 1 to ubound(b,1)
for k = 1 to ubound(c,1)
rw = rw +1
sh.cells(rw,1) = a(i,1)
sh.cells(rw,2) = b(j,1)
sh.cells(rw,3) = c(k,1)
next
next
Next

While this should work with the fixed information you show, you can expand
it to make it more robust and dynamic.

 

[JamRock]

Tom,
Thanks for your response, but the challenge for me is to write the code
dynamic enough,
so that the user can update the input range with new data and re-run
the same code to
rebuild the combinations sheet. You see, you coded 3 FOR LOOPs because
you know ahead
of time that there are 3 question type categories. I want the code to
determine the number
of question type categories and generate the combinations at run time.

To give a little more background. Once the combinations are generated I
will have code to
place some HLOOKUP VLOOKUP formulas in the colums to the right of the
combinations.
This part of the problem I think I can code fairly easily.

Once again thanks