Forum Discussion
Random Pool Generator excel office 365
Im trying to create a quiz generator. I have an excel file with 49 worksheets, and each worksheet covers one topic area and has a group of 20 or so related questions. Some groups have more questions, some less. I could make the worksheets into one big sheet if required, and just create a column to label each related topic area.
i want to create 30 randomly generated questions from that pool, with no more than two from any one topic area. Ive got a few ideas but nothing that seems relatively simple.
looking for formula based solution - not macro based.
TIA!
cincioh2002
Could you attach a sample file with some of the questions? This will help me understand your setup better.sorry i dont have a sample file i can post. It’s just a workbook with 49 tabs, and on each tab is three columns: question number, question, and answer. I was going to create one additional worksheet that was a quiz template which would have 30 questions on it pulled from the other tabs and use the random between function to pull a question from each tab. That will be too many questions though if i do 1 question from each tab - i only want 30 questions, but i need to pull those 30 questions from the total population of 49 groups (total around 980 questions) with no more than 2 questions from any one tab/group.
So i was thinking of putting all the questions in one list, and numbering them by grouping - 1-01 would be the first question from the first group, 2-03 would be the third question from the second group. I could put the group number and question number in separate columns. Either way, i was trying to think of a way to use one of the random functions to pull 30 questions, with no more than 2 from any single group of questions.
Hope that helps.
- Then, you need to consolidate all questions into one master sheet.
- Add column for identifier
Formula: =TEXT(A2,"00") & "-" & TEXT(B2,"00") - Add column for random questions with limitation of no more than 2 from any group.
Random column =RAND()Index Column, =IF(COUNTIF($A$2:A2, A2) <= 2, COUNTIF($A$2:A2, A2), "")
- Select all data (ctrl+a), and goto 'Sort & Filter' and click 'Custom Sort'
- You will go on Custom Sort window, click 'Add' to have another sort level,
sort by: Group Number & then by: Random, and press 'Ok' - Filter the Index Column, then uncheck '(Blanks)' and click 'Apply'
Final Result:
You will now have 2 questions on each group number.
If you want another set of questions, clear all the filter and do the step 4-6 again and you're done.
This is my approach, but it will be much easier if you'll add a little bit of macro (skipping the step 4-6)- Then, you need to consolidate all questions into one master sheet.
