Random Multiple choice questions

[apgmin]

In column A , I have a set of Multiple choice questions as part of a question bank. Columns B, C, D & E has the 4 options for the questions
Like Say in A1 if there is a question, then cells B1, C1, D1 & E1 has the 4 options to that question and Similarly for in A2 if there is a question, then cells B2, C2, D2 & E2 has the 4 options to that question

Now out of the 4 probable answers to the questions listed in cell B,C, D & E there is only a single correct answer

Now What I have done is used a formula to make sets of random 10 questions from the above list in column A =INDEX(A$1:A$100,RANDBETWEEN(1,ROWS(A$1:A$100)),1), this works fine

But I want the sequence of the answers to be also randomized in the subsequent columns and another column which says the correct answer

 

[BSALV]

with that formula, you can't avoid to get duplicate questions !

 

[bebo021999]

Pls provide a sample worksheet screenshot

 

[BSALV]

A:F are 100 rows with your questions and multiple choices, column G is arandom number 0-1
Column K are indexes for your 10 questions, column L is an unique random number between 1-100, and the following columns are the corresponding questions and multiple choices.
perhaps set the calculation at manual

Map1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	A1	B1	C1	D1	E1	F1	0,25219				question	row	question	option1	option2	option3	option4	option5
2	A2	B2	C2	D2	E2	F2	0,828978				1	29	A29	B29	C29	D29	E29	F29
3	A3	B3	C3	D3	E3	F3	0,873429				2	90	A90	B90	C90	D90	E90	F90
4	A4	B4	C4	D4	E4	F4	0,273281				3	75	A75	B75	C75	D75	E75	F75
5	A5	B5	C5	D5	E5	F5	0,816222				4	63	A63	B63	C63	D63	E63	F63
6	A6	B6	C6	D6	E6	F6	0,777814				5	24	A24	B24	C24	D24	E24	F24
7	A7	B7	C7	D7	E7	F7	0,84198				6	86	A86	B86	C86	D86	E86	F86
8	A8	B8	C8	D8	E8	F8	0,499516				7	85	A85	B85	C85	D85	E85	F85
9	A9	B9	C9	D9	E9	F9	0,542749				8	37	A37	B37	C37	D37	E37	F37
10	A10	B10	C10	D10	E10	F10	0,512404				9	93	A93	B93	C93	D93	E93	F93
11	A11	B11	C11	D11	E11	F11	0,402008				10	42	A42	B42	C42	D42	E42	F42
12	A12	B12	C12	D12	E12	F12	0,846647
13	A13	B13	C13	D13	E13	F13	0,631427
Blad1

Cell Formulas
Range	Formula
L2:L11	L2	=MATCH(SMALL($G$1:$G$100,K2),$G$1:$G$100,0)
M2:R11	M2	=INDEX($A$1:$G$100,$L2,COLUMN()-COLUMN($L$1))
G1:G13	G1	=RAND()

 

[apgmin]

the questions have become randomised. but the options are not randomized.

Like if Mr.X gets the question No. 8 as the 1st question and Mr.Y gets the same question No. 8 as the 5th question the sequence of options remains the same

 

[BSALV]

apgmin.xlsb
	K	L	M	N	O	P	Q	R	S
1	question		sort questions	question	answer1	answer2	answer3	answer4	correct answer
2	1	11	4	A11	B11	D11	E11	C11	F11
3	2	6	11	A6	C6	E6	B6	D6	F6
4	3	5	2	A5	B5	C5	E5	D5	F5
5	4	13	21	A13	E13	C13	B13	D13	F13
6	5	9	14	A9	D9	B9	E9	C9	F9
7	6	12	16	A12	D12	C12	E12	B12	F12
8	7	4	16	A4	D4	C4	E4	B4	F4
9	8	7	6	A7	B7	E7	D7	C7	F7
10	9	3	18	A3	D3	E3	C3	B3	F3
11	10	8	19	A8	E8	B8	C8	D8	F8
Blad1

Cell Formulas
Range	Formula
L2:L11	L2	=RANK(G1,$G$1:$G$13)
M2:M11	M2	=RANDBETWEEN(1,24)
N2:N11	N2	=INDEX($A$1:$F$13,$L2,1)
O2:R11	O2	=INDEX($A$1:$F$13,$L2,1+INDEX(permutaties!$A$1:$D$24,Blad1!$M2,COLUMN()-COLUMN(Blad1!$N$1)))
S2:S11	S2	=INDEX($A$1:$F$13,$L2,6)

just a macro to create all permutations with 4 elements
apgmin

 

[apgmin]

apgmin.xlsb
	K	L	M	N	O	P	Q	R	S
1	question		sort questions	question	answer1	answer2	answer3	answer4	correct answer
2	1	11	4	A11	B11	D11	E11	C11	F11
3	2	6	11	A6	C6	E6	B6	D6	F6
4	3	5	2	A5	B5	C5	E5	D5	F5
5	4	13	21	A13	E13	C13	B13	D13	F13
6	5	9	14	A9	D9	B9	E9	C9	F9
7	6	12	16	A12	D12	C12	E12	B12	F12
8	7	4	16	A4	D4	C4	E4	B4	F4
9	8	7	6	A7	B7	E7	D7	C7	F7
10	9	3	18	A3	D3	E3	C3	B3	F3
11	10	8	19	A8	E8	B8	C8	D8	F8
Blad1

Cell Formulas
Range	Formula
L2:L11	L2	=RANK(G1,$G$1:$G$13)
M2:M11	M2	=RANDBETWEEN(1,24)
N2:N11	N2	=INDEX($A$1:$F$13,$L2,1)
O2:R11	O2	=INDEX($A$1:$F$13,$L2,1+INDEX(permutaties!$A$1:$D$24,Blad1!$M2,COLUMN()-COLUMN(Blad1!$N$1)))
S2:S11	S2	=INDEX($A$1:$F$13,$L2,6)

just a macro to create all permutations with 4 elements
apgmin

everything works fine but not the random answers options to the questions ie. Answer 1-4. Also in the correct answer can it give the results as option 1 or 2 or 3 or 4, this will help us a lot as then we will not have to go through the options and match the right options to evaluate the responses because the correct answer will be one of the 4 options so instead of the correct answer can we get the option No.

 

[BSALV]

* Why aren't those 4 answers random, ? The 1st row is BDEC, the 2nd CEBD, ... . How is your random ?
* The correct answer in the F-column for the 11th question is for example D11.
Our ideas about this question aren't the same.
Can you give an example (with the XL2BB-tool or with a link or an image ) how it should work ?

 

[apgmin]

I think this is the problem permutaties! & Blad1!, most probably
they are other sheets in your file which I do not have