Question bank for Match the Following

[QuestionBaker]

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

 

[oldbrewer]

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]

student answer

correct / wrong

correct answer

1

boiling point of water in deg F

100

incorrect

212

2

opposite of hot

cold

correct

3

117 divided by 9

17

incorrect

13

pretend answers sheet

53 multiplied by 97

5141

this works but student can see correct answer as soon as they put their answer in

capital of england

London

opposite of hot

cold

how many days in March

31

boiling point of water in deg F

212

117 divided by 9

13

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

 

[QuestionBaker]

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 1	Right column Match 1
Left column Match 2	Right column Match 2
Left column Match 3	Right column Match 3
Left column Match 4	Right column Match 4
Left column Match ...	Right column Match ...
Left column Match ...	Right column Match ...
Left column Match 45	Right column Match 45

<tbody>
</tbody>

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

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

<tbody>
</tbody>

if I recalculate, then it should change again

Left column Match 21	Right column Match 45
Left column Match 45	Right column Match 6
Left column Match 6	Right 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]

117 divided by 9

how many days in March

53 multiplied by 97

col K

x

0.549617502

53 multiplied by 97

5141

0.202512707

capital of england

London

0.088621231

opposite of hot

cold

x

0.560434288

how many days in March

31

0.236468256

boiling point of water in deg F

212

x

0.566066418

117 divided by 9

13

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]

1	117 divided by 9			how many questions ?	3
2	boiling point of water in deg F
3	capital of england
4
5
6
7
8
9											col L
10
							row 13			0.053504527	5 x 5
											0.592458248	capital of england
											0.468700111	opposite of hot
											0.298165441	how many days in March
											0.622696698	boiling point of water in deg F
											0.647821188	117 divided by 9
			F1 can be any number 1 to 10
			this macro does it
											1	117 divided by 9
											2	boiling point of water in deg F
			Sub Macro4()								3	capital of england
			'								4
			' Macro4 Macro								5
			' Macro recorded 12/04/2019 by bob							6
			'								7
											8
			'								9
			Range("B1:B10").Select							10
			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]

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 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.

 

[oldbrewer]

glad to have been part of the solution - good luck !