Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

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?
1
leslieinva
Asked:
leslieinva
  • 10
  • 8
  • 5
  • +2
1 Solution
 
jerryb30Commented:
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
0
 
jerryb30Commented:
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?
0
 
leslieinvaAuthor Commented:
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
jerryb30Commented:
I'll look at an Access solution in VBA. Someone else might find a quicker solution in Excel.
0
 
Rob HensonIT & Database AssistantCommented:
Something like that attached.
Question---Answers.xlsx
0
 
leslieinvaAuthor Commented:
A maximum of number of questions: 10.  Maximum number of answers per question: 5.  

Yes this could be a lot of records.
0
 
Rob HensonIT & Database AssistantCommented:
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
0
 
leslieinvaAuthor Commented:
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.
0
 
jerryb30Commented:
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
0
 
Rob HensonIT & Database AssistantCommented:
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
0
 
leslieinvaAuthor Commented:
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.
0
 
Rob HensonIT & Database AssistantCommented:
Sorry, 10:00pm in UK so going have to bow out; brain frazzled.
0
 
Rob HensonIT & Database AssistantCommented:
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
0
 
Gustav BrockCIOCommented:
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);

Open in new window

/gustav
Survey.accdb
0
 
leslieinvaAuthor Commented:
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.
0
 
leslieinvaAuthor Commented:
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.
0
 
Gustav BrockCIOCommented:
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
0
 
Rob HensonIT & Database AssistantCommented:
Forming the logic I used into a macro shouldn't take too much. I will see what I can come up with.
0
 
Harry LeeCommented:
Leslie,

So far, this is the best I can come up with.

Give it a try.

It's a huge loop macro.
Combined-Results.xlsb
0
 
Rob HensonIT & Database AssistantCommented:
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
0
 
jerryb30Commented:
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.
0
 
leslieinvaAuthor Commented:
Yeah, this works.
0
 
Harry LeeCommented:
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.
0
 
leslieinvaAuthor Commented:
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.
0
 
Rob HensonIT & Database AssistantCommented:
The script I was working on was not limited to 10 so will revisit tomorrow.
0
 
Harry LeeCommented:
Thanks Robhenson!!
0
 
Rob HensonIT & Database AssistantCommented:
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
0
 
Harry LeeCommented:
robhenson,

I have tried your macro. If you change the question and answers to real questions and answers, your macro do not work.
0
 
Rob HensonIT & Database AssistantCommented:
The question asked was for question number and letter for answer.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 10
  • 8
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now