asked on # How could I generate every possible question/answer combination?

I need to generate a list showing every possible combination of answers to several questions.

Each row in this list should be another possible combination of answers.

For example, there might be two questions (question 1 & question 2), question 1 has two possible responses (a or b), question 2 has three possible response (a, b, or c). Below is the kind of list I need showing each possible combination on a separate row:

1a, 2a

1a, 2b

1b, 2a

1b, 2b

1b, 2c

In Excel or Access or some other common tool, I'd like to specify the number of questions & number of answers for each question & get a list or table like the one above. Is this possible using VBA or something else available via MS Office or on the web?

Each row in this list should be another possible combination of answers.

For example, there might be two questions (question 1 & question 2), question 1 has two possible responses (a or b), question 2 has three possible response (a, b, or c). Below is the kind of list I need showing each possible combination on a separate row:

1a, 2a

1a, 2b

1b, 2a

1b, 2b

1b, 2c

In Excel or Access or some other common tool, I'd like to specify the number of questions & number of answers for each question & get a list or table like the one above. Is this possible using VBA or something else available via MS Office or on the web?

Microsoft ExcelMicrosoft AccessVisual Basic Classic

Bear in mind, this could be a large number of records. With the small sample I posted, you would have 2*3*5*2*2 (120) records.

Any upper limit to number of possible answers, and number of questions?

Any upper limit to number of possible answers, and number of questions?

Jerryb30,

Yes, I could enter the parameters in the format you mentioned as a table of question number, and number of possible answers.

I would prefer if the function knew that the answers were named "a" through however many answers were possible, so that in the results answers were identified as letters.

Yes, I could enter the parameters in the format you mentioned as a table of question number, and number of possible answers.

I would prefer if the function knew that the answers were named "a" through however many answers were possible, so that in the results answers were identified as letters.

I'll look at an Access solution in VBA. Someone else might find a quicker solution in Excel.

Something like that attached.

Question---Answers.xlsx

A maximum of number of questions: 10. Maximum number of answers per question: 5.

Yes this could be a lot of records.

Yes this could be a lot of records.

The number of records does not have to be multiplied together like jerryb30 is suggesting. They will merely be added together.

If there are 10 questions and all have 2 answers there are only 20 possible combinations:

2+2+2+2+2+2+2+2+2+2 = 20

Not 2*2*2*2*2*2*2*2*2*2 = 1024

New file attached with 10 questions, assumed same number of answers as 1 to 5.

Question---Answers.xlsx

If there are 10 questions and all have 2 answers there are only 20 possible combinations:

2+2+2+2+2+2+2+2+2+2 = 20

Not 2*2*2*2*2*2*2*2*2*2 = 1024

New file attached with 10 questions, assumed same number of answers as 1 to 5.

robhenson,

I don't see all the possible combinations of questions & answers in your spreadsheet, only a list of the questions & their answers. For example, every combination in which some could answer 1a would include 1a, 2a, 3a, 4a, 5a and 1a, 2b, 3a, 4a, 5a and 1a, 2a, 3b, 4a, 5a, etc.

I don't see all the possible combinations of questions & answers in your spreadsheet, only a list of the questions & their answers. For example, every combination in which some could answer 1a would include 1a, 2a, 3a, 4a, 5a and 1a, 2b, 3a, 4a, 5a and 1a, 2a, 3b, 4a, 5a, etc.

Just 3 questions with 2 possible answers each gives you

1a,2a, 3a

1a, 2b, 3a

1a, 2a, 3b

1a, 2b, 3b

1b,2a, 3a

1b, 2b, 3a

1b, 2a, 3b

1b, 2b, 3b

0r

2^3

1a,2a, 3a

1a, 2b, 3a

1a, 2a, 3b

1a, 2b, 3b

1b,2a, 3a

1b, 2b, 3a

1b, 2a, 3b

1b, 2b, 3b

0r

2^3

Apologies, I had thought it was too simple.

I had read it as combinations for each question on its own.

For the number of combinations, there will be an awful lot.

If all 10 questions had 5 options, the combinations would be 1+(5^9) or 1,953,126 !!!

If all had 2 options it would be 1+(2^9) or 513.

This is because you would have Answer 1 and then 9 other questions with up to 5 answers.

Dare I ask why you need a list of all possible answers??

Thanks

Rob H

I had read it as combinations for each question on its own.

For the number of combinations, there will be an awful lot.

If all 10 questions had 5 options, the combinations would be 1+(5^9) or 1,953,126 !!!

If all had 2 options it would be 1+(2^9) or 513.

This is because you would have Answer 1 and then 9 other questions with up to 5 answers.

Dare I ask why you need a list of all possible answers??

Thanks

Rob H

Rob H,

I'm working on providing recommendations based on answers to several questions.

I don't know how many questions there will be or how many answers each will have yet, but when I do know, I'll need to know every possible combination, so users don't provide answers that we failed to consider when formulating recommendations.

I'm working on providing recommendations based on answers to several questions.

I don't know how many questions there will be or how many answers each will have yet, but when I do know, I'll need to know every possible combination, so users don't provide answers that we failed to consider when formulating recommendations.

Sorry, 10:00pm in UK so going have to bow out; brain frazzled.

Inquisitive part of frazzled brain wouldn't let go!

I think I have sussed it with the data set I had and it didn't take long doing it manually, no doubt could run a macro to do it matter of seconds.

See attached, now with a table to the right with 14000+ rows of combinations.

Logic behind creation of table:

Question 1 - option a: 3 possible combinations with Q2 which has 3 options. Copy and paste 1a times 3 and add 2a, 2b & 2c in separate rows.

Question 3 - 5 options. Copy entire table and paste below 4 more times. Add 3a, 3b, 3c, 3d, 3e against each block.

Question 4 - 2 options. Copy entire table and paste below. Add 4a and 4b against each block.

Question 5 - 2 options. Copy and paste as above.

I hope you get the gist by now.

However, realised that we didn't have both options for Q1, copy whole table and paste below. Change new block in column 1 to 1b.

Thanks

Rob H

Question---Answers.xlsx

I think I have sussed it with the data set I had and it didn't take long doing it manually, no doubt could run a macro to do it matter of seconds.

See attached, now with a table to the right with 14000+ rows of combinations.

Logic behind creation of table:

Question 1 - option a: 3 possible combinations with Q2 which has 3 options. Copy and paste 1a times 3 and add 2a, 2b & 2c in separate rows.

Question 3 - 5 options. Copy entire table and paste below 4 more times. Add 3a, 3b, 3c, 3d, 3e against each block.

Question 4 - 2 options. Copy entire table and paste below. Add 4a and 4b against each block.

Question 5 - 2 options. Copy and paste as above.

I hope you get the gist by now.

However, realised that we didn't have both options for Q1, copy whole table and paste below. Change new block in column 1 to 1b.

Thanks

Rob H

You need one table and one query for this output:

Question Answer

1a 2a

1a 2b

1b 2a

1b 2b

1b 2c

2a 2a

2a 2b

Query:

Survey.accdb

Question Answer

1a 2a

1a 2b

1b 2a

1b 2b

1b 2c

2a 2a

2a 2b

Query:

```
SELECT
[MainQuestion] & [SubQuestion] AS Question,
"2" & Chr(Asc("a")+(10+[MSysObjects]![Id] Mod 10)-1) AS Answer
FROM
MSysObjects,
Questions
WHERE
(((10+[MSysObjects]![Id] Mod 10)<=[Answers]))
GROUP BY
[MainQuestion] & [SubQuestion],
"2" & Chr(Asc("a")+(10+[MSysObjects]![Id] Mod 10)-1);
```

/gustav
Rob H,

Thanks for trying, but I'm looking for a macro more than a method. I don't want to do as much work as you have done. I want to give a function a number of questions & how many answers there are for each question & have it generate the table of combinations, not me.

Thanks for trying, but I'm looking for a macro more than a method. I don't want to do as much work as you have done. I want to give a function a number of questions & how many answers there are for each question & have it generate the table of combinations, not me.

Gustav,

I like your approach of starting with a table specifing the inputs & using a SQL statement. However I don't know what is meant by a "mainquestion" or "subquestion" because there's only one type of question. In my examples, the letters "a" or "b" signify possible answers to a question, not subquestion.

I'm looking for the possible combinations of answers to all the questions, so a row of 2a 2b in the results would make no sense. You can only select one answer for each question, either 2a or 2b.

I like your approach of starting with a table specifing the inputs & using a SQL statement. However I don't know what is meant by a "mainquestion" or "subquestion" because there's only one type of question. In my examples, the letters "a" or "b" signify possible answers to a question, not subquestion.

I'm looking for the possible combinations of answers to all the questions, so a row of 2a 2b in the results would make no sense. You can only select one answer for each question, either 2a or 2b.

I see (or not).

So why is there no "1a, 2c" in your sample:

1a, 2a

1a, 2b

1b, 2a

1b, 2b

1b, 2c

/gustav

So why is there no "1a, 2c" in your sample:

1a, 2a

1a, 2b

1b, 2a

1b, 2b

1b, 2c

/gustav

Forming the logic I used into a macro shouldn't take too much. I will see what I can come up with.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

Getting there with macro to replicate my logic but not quite there.

So far running the macro with the options in previous submission takes 4 seconds to run but the output is currently incomplete. Now trying to work out why but I don't think it will increase the time to run by much.

Thanks

Rob H

So far running the macro with the options in previous submission takes 4 seconds to run but the output is currently incomplete. Now trying to work out why but I don't think it will increase the time to run by much.

Thanks

Rob H

This is harder than I thought it would be. If you could post the number of answers for question 1-10, I could possibly do a one-off, if you don't get something from the others.

At any rate, it IS an interesting exercise.

At any rate, it IS an interesting exercise.

Yeah, this works.

This is definitely way harder than I originally thought.

Wanted to do it all with Array, but can't can it to work yet.

So, the 10 layer nested loop is so for the best I can think of. Of course, it is insanely slow if all 10 questions have 5 answers.

There are a lots of disadvantages to do it this way. With nested for loops, it limited the number of questions to 10.

I was so hoping someone can help getting an array version done so that the number of questions can be dynamic.

Wanted to do it all with Array, but can't can it to work yet.

So, the 10 layer nested loop is so for the best I can think of. Of course, it is insanely slow if all 10 questions have 5 answers.

There are a lots of disadvantages to do it this way. With nested for loops, it limited the number of questions to 10.

I was so hoping someone can help getting an array version done so that the number of questions can be dynamic.

It worked pretty fast for the examples I have to cover so far.

That would be cool if it could be allow for questions, but I think 10 will cover the instances I will run into.

That would be cool if it could be allow for questions, but I think 10 will cover the instances I will run into.

The script I was working on was not limited to 10 so will revisit tomorrow.

Thanks Robhenson!!

Got there in the end.

See attached.

**Question sheet:**

Column B - list of question numbers. This can be as long as required. This generates a dynamic Named Range called Q_List, using the OFFSET function counting the number of entries in column B.

Columns C to I - possible answers

Column A - count of possible answers.

You don't have to populate columns C to I with answers if you don't want but you do need a value in column A so this can be overwritten. The alpha tag for each question eg 1a, 1b etc will be generated within the script using the ASCII character code.

M19 to N21 - Start and Finish times populated to calculate how long routine takes to run.

N1 (Hidden by button) - Range name "AnsRows", counts number of entries on Results Sheet.

Button - Click to Generate Results.

**Results Sheet**

List of possible Results for questions. Start of routine clears sheet before populating.

I tried running for 10 questions with 5 answers each and the results sheet ran out of space. Trial and error leads me to believe that the most question options that you can have is 42, so 8 questions with 5 answers plus one with 2 answers.

I can implement a check on this basis if so required.

Thanks

Rob H

Question---Answers.xlsm

See attached.

Column B - list of question numbers. This can be as long as required. This generates a dynamic Named Range called Q_List, using the OFFSET function counting the number of entries in column B.

Columns C to I - possible answers

Column A - count of possible answers.

You don't have to populate columns C to I with answers if you don't want but you do need a value in column A so this can be overwritten. The alpha tag for each question eg 1a, 1b etc will be generated within the script using the ASCII character code.

M19 to N21 - Start and Finish times populated to calculate how long routine takes to run.

N1 (Hidden by button) - Range name "AnsRows", counts number of entries on Results Sheet.

Button - Click to Generate Results.

List of possible Results for questions. Start of routine clears sheet before populating.

I tried running for 10 questions with 5 answers each and the results sheet ran out of space. Trial and error leads me to believe that the most question options that you can have is 42, so 8 questions with 5 answers plus one with 2 answers.

I can implement a check on this basis if so required.

Thanks

Rob H

robhenson,

I have tried your macro. If you change the question and answers to real questions and answers, your macro do not work.

I have tried your macro. If you change the question and answers to real questions and answers, your macro do not work.

The question asked was for question number and letter for answer.

As in:

1-2

2-3

3-5

4-2

5-2