Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help generating combinations
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help generating combinations
Hi Group
Any help will be appreciated, even an indication of the best place to post the question. Thanks and regards |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help generating combinations
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. -- Regards, Tom Ogilvy "JamRock" wrote in message oups.com... Hi Group Any help will be appreciated, even an indication of the best place to post the question. Thanks and regards |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help generating combinations
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Generating a list of all possible number/letter combinations | Excel Programming | |||
Generating Possible Combinations and Assigning ID Number | Excel Worksheet Functions | |||
Generating excel combinations | Excel Discussion (Misc queries) | |||
Function generating all possible combinations of set of numbers | Excel Worksheet Functions | |||
a problem in generating a list of all of the possible combinations of those words | Excel Programming |