Question bank for Match the Following

QuestionBaker

New Member
Joined
Apr 12, 2019
Messages
46
Office Version
  1. 365
Platform
  1. Windows
Tl dr; I am trying to make a question bank where one of the question is match the following and I want to randomly select them to form an assignment.

I am trying to make question bank in excel so that I can hand out a separate assignment sheet to each student. There are different types of questions like fill in the blank, translate the sentence or write the opposite of the word etc. I've been able to randomize most of the questions barring few eg. Match the Following.

Finally I want to generate a match the following question that is randomly selected from the a sheet called MtF (which stands for Match the following which has all question and their matches in the correct order), its correct answer is also selected and then they are randomly shuffled. I should be should be able to add as many questions and answer in MtF as I want and also increase number of questions I can give to the students.

Context: I am trying to create custom assignment for students that we teach as a part of CSR activity and I want to randomize them so that they do not cheat and try and solve on their own.

Thanks
QuestionBaker
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005
if the questions and answers are on sheet 2 and sheet 2 cannot be seen or viewed ( called deep protection ) should work fine, example to follow
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005
student answercorrect / wrongcorrect answer
1boiling point of water in deg F100incorrect212
2opposite of hotcoldcorrect
3117 divided by 917incorrect13
pretend answers sheet
53 multiplied by 975141
this works but student can see correct answer as soon as they put their answer incapital of englandLondon
opposite of hotcold
how many days in March31
boiling point of water in deg F212
117 divided by 913

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>
 

QuestionBaker

New Member
Joined
Apr 12, 2019
Messages
46
Office Version
  1. 365
Platform
  1. Windows
I think I may have not cleared some stuff.

I am not handing over the assignments in excel, just using it to create different ones. The idea is to print a different assignment for each kid.

Let me try and explain what I am trying to do

So, Mtf would look something like this

Left column Match 1Right column Match 1
Left column Match 2Right column Match 2
Left column Match 3Right column Match 3
Left column Match 4Right column Match 4
Left column Match ...Right column Match ...
Left column Match ...Right column Match ...
Left column Match 45Right column Match 45

<tbody>
</tbody>


and I want something like the following to appear in the Assignment sheet

Left column Match 2Right column Match 38
Left column Match 38Right column Match 2
Left column Match 16Right column Match 16

<tbody>
</tbody>

if I recalculate, then it should change again

Left column Match 21Right column Match 45
Left column Match 45Right column Match 6
Left column Match 6Right column Match 21

<tbody>
</tbody>

so on and so forth, I should also be able to increase from just giving 3 questions to 4 or 5 or 6 questions.

Currently, I've something similar with random numbers.

I rank the random numbers, use it as an index and for
Left column Match
then for the answers, I use the same method and point to the
Right column Match

then I create a new column having exactly as many numbers of random numbers as I want to give the questions. So, in this case, I would generate 3 random numbers. I use them to randomly arrange the answers

The problem with this method is that it is very difficult to scale it up.

Is it possible to upload a file in here?

How can I best explain what I want and what I've managed to do till now?
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005

ADVERTISEMENT

117 divided by 9
how many days in March
53 multiplied by 97
col K
x0.54961750253 multiplied by 975141
0.202512707capital of englandLondon
0.088621231opposite of hotcold
x0.560434288how many days in March31
0.236468256boiling point of water in deg F212
x0.566066418117 divided by 913
a macro that presses F9 and copy paste special values col K
your selection is made, say by finding the 3 biggest numbers in
col K and transferring the associated questions
to the student test paper
it is most unlikey there will be duplicates
as the random numbers are 9 digits
run the macro again and the order of the random
nubers will change and you will get a new set of questions
A3 (117 divided by 9 ) generated by this formula
=OFFSET($K$12,MATCH(LARGE($K$13:$K$18,1),$K$13:$K$18,0),1)
the 1 in the LARGE bracket is replaced by 2 and 3 for the next 2 questions

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005
1117 divided by 9how many questions ?3
2boiling point of water in deg F
3capital of england
4
5
6
7
8
9col L
10
row 130.0535045275 x 5
0.592458248capital of england
0.468700111opposite of hot
0.298165441how many days in March
0.622696698boiling point of water in deg F
0.647821188117 divided by 9
F1 can be any number 1 to 10
this macro does it
1117 divided by 9
2boiling point of water in deg F
Sub Macro4()3capital of england
'4
' Macro4 Macro5
' Macro recorded 12/04/2019 by bob6
'7
8
'9
Range("B1:B10").Select10
Selection.ClearContents
For j = 13 To 18
Cells(j, 12) = Rnd
Next j
Range("L24:M33").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="6"></colgroup><tbody>
</tbody>
 

QuestionBaker

New Member
Joined
Apr 12, 2019
Messages
46
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thank you for the help. Even though I did not end up using exactly as you advised, but it did inspire to come up with a solution that I was comfortable with.

This is how it finally looks
Sheet containing the assignment: Assignment

A | B | C| D | E| F |
55 | Q9 | | Match the following | | | |
56 | | a | Option R | 1 | Answer of Option G | =RAND() |
57
[FONT=&quot] | | [/FONT]b | Option A | 2 | Answer of Option H[FONT=&quot] | =RAND() |[/FONT]
58
[FONT=&quot] | | [/FONT]c | Option G | 3 | Answer of Option D[FONT=&quot] | =RAND() |[/FONT]
59
[FONT=&quot] | | [/FONT]d | Option H [FONT=&quot] [/FONT] | 4 | Answer of Option A[FONT=&quot] | =RAND() |[/FONT]
60
[FONT=&quot] | | [/FONT]e | Option D [FONT=&quot] [/FONT] | 5 | Answer of Option R[FONT=&quot] | =RAND() |[/FONT]

Column F is not in the print zone. and remains in the back-end when the assignment is printed

Match the Following Question Bank Sheet: MtF
[FONT=&quot] | A[/FONT][FONT=&quot] [/FONT][FONT=&quot]| B[/FONT][FONT=&quot]| C[/FONT][FONT=&quot]|
1 | =RAND() | Option A | [/FONT][FONT=&quot]Answer of Option A |
[/FONT][FONT=&quot] 2 | =RAND() | Option B | [/FONT][FONT=&quot]Answer of Option B |
[/FONT][FONT=&quot] 3 | =RAND() | Option C | [/FONT][FONT=&quot]Answer of Option C |
[/FONT][FONT=&quot] 4 | =RAND() | Option D | [/FONT][FONT=&quot]Answer of Option D |
[/FONT][FONT=&quot] 5 | =RAND() | Option E | [/FONT][FONT=&quot]Answer of Option E |
[/FONT][FONT=&quot]...| =RAND() | Option... | [/FONT][FONT=&quot]Answer of Option... |[/FONT][FONT=&quot]
[/FONT][FONT=&quot]26 | =RAND() | Option Z | [/FONT][FONT=&quot]Answer of Option Z |

[/FONT]Formulae used are as follows

Cell : Assignment!C56
Formula : =INDEX('MtF'!B:B,RANK.EQ('MtF'!A1,'MtF1'!A:A))
Description : Checks the ranks of cell MtF!A1 with respect to column A of MtF. Then prints the cell at that position from column B

eg. if rank of MtF!A1 = 5, the Assignment!C56 would print MtF!B5

[FONT=&quot]Cell : Assignment!E56[/FONT]
[FONT=&quot]Formula : [/FONT]=INDEX('MtF'!C:C,RANK.EQ(INDEX('MtF'!A:A,RANK.EQ(F56,F:F)),'MtF'!A:A))
[FONT=&quot]Description : Checks the ranks of cell Assignment!F56 with respect to column F of Assignment. Then goes to that position in column A of MtF. repeats the same task as it did for Assignment!C56 but prints from column C instead of column B.[/FONT]

eg. let,
rank of Assignment!F56 = 3,
rank of MtF!A3 =14
then AssignmentC!56 would print MtF!C14

Caution: No numbers should be added in column F of sheet Assignment, otherwise shuffling may go wrong, I am not sure how text would affect it either, at best I would recommend to avoid everything
If you want to print one more math the following, I would suggest use a different column to for =RAND() instead of Assignment column F

PS. There might be slight mistakes in the formula as my cells were a bit different due to formatting and cell merging above in the document, hence I've also given description so one may be able to figure out what and how I've done if there is a mistake in the formula.
 

QuestionBaker

New Member
Joined
Apr 12, 2019
Messages
46
Office Version
  1. 365
Platform
  1. Windows
New here, so the formatting is not on point, and it sad since I took decent time to make sure it is more easily readable when I was typing by correctly maintaining spaces and all other stuff

Thank you for the help. Even though I did not end up using exactly as you advised, but it did inspire to come up with a solution that I was comfortable with.

This is how it finally looks
Sheet containing the assignment: Assignment

A | B | C| D | E| F |
55 | Q9 | | Match the following | | | |
56 | | a | Option R | 1 | Answer of Option G | =RAND() |
57
| | b | Option A | 2 | Answer of Option H | =RAND() |
58
| | c | Option G | 3 | Answer of Option D | =RAND() |
59
| | d | Option H | 4 | Answer of Option A | =RAND() |
60
| | e | Option D | 5 | Answer of Option R | =RAND() |

Column F is not in the print zone. and remains in the back-end when the assignment is printed

Match the Following Question Bank Sheet: MtF
| A| B| C|
1 | =RAND() | Option A |
Answer of Option A |
2 | =RAND() | Option B | Answer of Option B |
3 | =RAND() | Option C | Answer of Option C |
4 | =RAND() | Option D | Answer of Option D |
5 | =RAND() | Option E | Answer of Option E |
...| =RAND() | Option... | Answer of Option... |
26 | =RAND() | Option Z | Answer of Option Z |

Formulae used are as follows

Cell : Assignment!C56
Formula : =INDEX('MtF'!B:B,RANK.EQ('MtF'!A1,'MtF1'!A:A))
Description : Checks the ranks of cell MtF!A1 with respect to column A of MtF. Then prints the cell at that position from column B

eg. if rank of MtF!A1 = 5, the Assignment!C56 would print MtF!B5

Cell : Assignment!E56
Formula : =INDEX('MtF'!C:C,RANK.EQ(INDEX('MtF'!A:A,RANK.EQ(F56,F:F)),'MtF'!A:A))
Description : Checks the ranks of cell Assignment!F56 with respect to column F of Assignment. Then goes to that position in column A of MtF. repeats the same task as it did for Assignment!C56 but prints from column C instead of column B.

eg. let,
rank of Assignment!F56 = 3,
rank of MtF!A3 =14
then AssignmentC!56 would print MtF!C14

Caution: No numbers should be added in column F of sheet Assignment, otherwise shuffling may go wrong, I am not sure how text would affect it either, at best I would recommend to avoid everything
If you want to print one more math the following, I would suggest use a different column to for =RAND() instead of Assignment column F

PS. There might be slight mistakes in the formula as my cells were a bit different due to formatting and cell merging above in the document, hence I've also given description so one may be able to figure out what and how I've done if there is a mistake in the formula.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,156
Latest member
WDMix

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
Top