How could I generate every possible question/answer combination?

So you might have a table of question number, and number of possible answers?
As in:
1-2
2-3
3-5
4-2
5-2

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?

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.

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.

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

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.

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

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

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.

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

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:

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);

Select allOpen in new window

/gustav
Survey.accdb

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.

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 see (or not).
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.

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

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.

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.

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.

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

robhenson,

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.