Solved

How could I generate every possible question/answer combination?

Posted on 2013-11-19
29
338 Views
1 Endorsement
Last Modified: 2013-11-22
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
Comment
Question by:leslieinva
  • 10
  • 8
  • 5
  • +2
29 Comments
 
LVL 26

Expert Comment

by:jerryb30
ID: 39660572
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 39660596
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
 

Author Comment

by:leslieinva
ID: 39660599
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 39660618
I'll look at an Access solution in VBA. Someone else might find a quicker solution in Excel.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39660695
Something like that attached.
Question---Answers.xlsx
0
 

Author Comment

by:leslieinva
ID: 39660700
A maximum of number of questions: 10.  Maximum number of answers per question: 5.  

Yes this could be a lot of records.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39660722
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
 

Author Comment

by:leslieinva
ID: 39660758
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 39660765
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39660798
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
 

Author Comment

by:leslieinva
ID: 39660815
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39660836
Sorry, 10:00pm in UK so going have to bow out; brain frazzled.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39660871
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39661772
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:leslieinva
ID: 39662645
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
 

Author Comment

by:leslieinva
ID: 39662674
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39663875
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39663952
Forming the logic I used into a macro shouldn't take too much. I will see what I can come up with.
0
 
LVL 12

Accepted Solution

by:
Harry Lee earned 500 total points
ID: 39663989
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39665763
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 39665850
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
 

Author Closing Comment

by:leslieinva
ID: 39665903
Yeah, this works.
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39666844
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
 

Author Comment

by:leslieinva
ID: 39666867
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39667635
The script I was working on was not limited to 10 so will revisit tomorrow.
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39667694
Thanks Robhenson!!
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39668615
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
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39669615
robhenson,

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

Expert Comment

by:Rob Henson
ID: 39670126
The question asked was for question number and letter for answer.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now