random selection based on multiple criteria

mrticklescience

New Member
Joined
Apr 18, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello. If anyone can help me with this it would be amazing. It is definitely above my skill level.
I am a teacher and I have written a bank of questions organised by a topic code. See below:

1650301887869.png



I would like to be able to quickly use this question bank to generate quizzes. However, I would like to be able to randomly select questions from specific topics and also specify the number of questions from each topic. For example: 1 question from 4.1.1.1, 2 questions from 4.1.1.3 and 2 questions from 4.1.1.4.

I would be really grateful if anyone can help me with this.

Thanks in advance,

Paul
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Welcome Mrticklescience.
For starters, here is a formula that should get you started. see example
It should list out the amount (at random) of entries you specify for the "Reference" you specify.

You will need to be more specific on where you want your results and how you general layout and formatting may work.

Book1
ABCDE
1Specific ReferenceValueSpecific Reference(lookup)How Many entries
24.1.1.14.1.1.1 - R14.1.1.23
34.1.1.14.1.1.1 - R2
44.1.1.24.1.1.2 - R14.1.1.2 - R15
54.1.1.24.1.1.2 - R24.1.1.2 - R1
64.1.1.24.1.1.2 - R34.1.1.2 - R13
74.1.1.24.1.1.2 - R4
84.1.1.24.1.1.2 - R5
94.1.1.24.1.1.2 - R6
104.1.1.24.1.1.2 - R7
114.1.1.24.1.1.2 - R8
124.1.1.24.1.1.2 - R9
134.1.1.24.1.1.2 - R10
144.1.1.24.1.1.2 - R11
154.1.1.24.1.1.2 - R12
164.1.1.24.1.1.2 - R13
174.1.1.24.1.1.2 - R14
184.1.1.24.1.1.2 - R15
194.1.1.34.1.1.3 - R1
204.1.1.34.1.1.3 - R2
214.1.1.34.1.1.3 - R3
224.1.1.34.1.1.3 - R4
234.1.1.34.1.1.3 - R5
244.1.1.34.1.1.3 - R6
254.1.1.34.1.1.3 - R7
264.1.1.34.1.1.3 - R8
274.1.1.34.1.1.3 - R9
284.1.1.34.1.1.3 - R10
294.1.1.34.1.1.3 - R11
304.1.1.34.1.1.3 - R12
314.1.1.34.1.1.3 - R13
324.1.1.34.1.1.3 - R14
334.1.1.34.1.1.3 - R15
Sheet1
Cell Formulas
RangeFormula
D4:D6D4=INDEX(FILTER($B:$B,$A:$A=$D$2),RANDARRAY($E$2,1,1,COUNTIF($A:$A,$D$2),TRUE))
Dynamic array formulas.
 
Upvote 0
Here is a similar idea. Helper columns to the right consist of formulas mostly, and a set of blue-shaded cells for setting the number of questions for each topic area. The quiz questions are randomly selected and printed below, refreshed by hitting F9. Be sure to copy the quiz question block and perform a Paste Special > Values somewhere else to preserve the questions, as any refreshes to the worksheet will generate new random numbers and change the selections.
MrExcel_20220418.xlsx
ABCDEFGHIJK
1Specification ReferenceQuestionQuestion NumberNumber of QuestionsTopic AreasRandomly Selected
24.1.1.1What are elements? Give an example.114.1.1.11
34.1.1.1What is a compound? Give an example.234.1.1.2653
44.1.1.2What are mixtures? Give an example.334.1.1.391012
54.1.1.2Describe when you would use filtration and what equipment you would need.414.1.1.417
64.1.1.2Describe when you would use crystallisation rather than evaporation and when should you stop heating?514.1.1.524
74.1.1.2Describe when you would use distillation and what equipment you would use.6
84.1.1.2Describe what fractional distillation is used for.7
94.1.1.2Describe what chromatography is used for.8
104.1.1.3What did people (e.g. Dalton) think the atom looked like before the electron was discovered?9
114.1.1.3State what]] Thomson discovered and describe his Plum Pudding model of the atom.10
124.1.1.3Describe the results of Rutherford's alpha particle scattering experiment.11
134.1.1.3What did Rutherford think the atom looked like? What was his model called?12
144.1.1.3Describe Niels Bohr's model of the atom?13
154.1.1.3Who discovered the proton? Was this before or after Bohr's nuclear model?14
164.1.1.3Who discovered the neutron and was this before or after the discover,' of the proton?15
174.1.1.4What is the relative charge of (a) a proton, (b) a neutron and (c) an electron.16
184.1.1.4Why are atoms neutral?17
194.1.1.4What does the atomic number of an atom tell us?18
204.1.1.4What is the radius of an atom?19
214.1.1.4What is the radius of the nucleus in comparison to the atom?20
224.1.1.4Where is all the mass of an atom found?21
234.1.1.4What is the relative mass of (a) a proton, (b) a neutron and (c) an electron.22
244.1.1.4What does the mass number of an atom tell you?23
254.1.1.5What are isotopes? Use 5C1 and Cl to help you explain your answer.24
26
27
28QUIZ
291What are elements? Give an example.
302What are mixtures? Give an example.
313Describe when you would use crystallisation rather than evaporation and when should you stop heating?
324Describe when you would use distillation and what equipment you would use.
335What did people (e.g. Dalton) think the atom looked like before the electron was discovered?
346State what]] Thomson discovered and describe his Plum Pudding model of the atom.
357What did Rutherford think the atom looked like? What was his model called?
368Why are atoms neutral?
379What are isotopes? Use 5C1 and Cl to help you explain your answer.
Sheet1
Cell Formulas
RangeFormula
D2:D25D2=SEQUENCE(ROWS(B2:B25))
F2:F6F2=UNIQUE(A2:A25)
G2,G5:G6,G3:I4G2=TRANSPOSE(INDEX(SORTBY(FILTER($D$2:$D$25,$A$2:$A$25=$F2),RANDARRAY(COUNTIF($A$2:$A$25,$F2))),SEQUENCE($E2)))
A29:A37A29=SEQUENCE(SUM($E$2:$E$6))
B29:B37B29=INDEX($B$2:$B$25,LET(list,LET(array,$G$2:$K$6, rowcount,ROWS(array), colcount,COLUMNS(array), itemcount,rowcount*colcount, MAKEARRAY(itemcount,1, LAMBDA(r,c, INDEX(array, LET(a,MOD(r,rowcount),IF(a=0,rowcount,a)), ROUNDUP(r/rowcount,0) ) ))),SORT(FILTER(list,list>0))))
Dynamic array formulas.

If you are unfamiliar with the XL2BB add-in, you can click on the link in my signature block and install it on your system. This makes sharing working examples much easier. You should be able to click on the clipboard icon in the upper left where the column and row headings intersect and then paste into your worksheet to copy all of the formulas and layout into one of your worksheets.
 
Upvote 0
Hi both,

Thank you so much for taking the time to reply. I genuinely appreciate it. I'm going to have a play around with both examples.

Thanks again!

Paul
 
Upvote 0
We're happy to help. The messiest part is devising some method for stacking the questions into a single list when the sublists (i.e., the number of questions on each topic) vary in length. In my example, the gray block of cells (formula in G2) shows the randomly selected question numbers (that refer to the column D helper list). Then the LET function (with an embedded LAMBDA function) in the B29 formula takes the question numbers in the gray block and stacks them into a column. Those question numbers are then fed to the INDEX function (also in B29) to extract the relevant question.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top