Is possible to shuffle answers in multiple choice test in EXCEL?

[t0m1noo]

I have a lot of questions and I need to quickly shuffle answers in Multiple Choice Test. (1 question has 4 answers, A,B,C, or D).
For example:

Is possible to do that by using VBA or with some formula?

Thanks for the help.

 

[GraH]

It may require some preparation and set-up, but do-able like this:
1. random using RAND()
2. rank to enable the lookup
3. lookup the rank in the final table
4. F9 will update the rank each time

Book1
	A	B	C	D	E	F	G
1	Question 1	random	rank			Question 1
2	a correct	0,590911	2			1	c correct
3	b false	0,120755	4			2	a correct
4	c correct	0,814065	1			3	d false
5	d false	0,227136	3			4	b false
6	Question 2					Question 2
7	a false	0,155751	3			1	d false
8	b correct	0,311927	2			2	b correct
9	c false	0,0893	4			3	a false
10	d false	0,487708	1			4	c false
11	Question 3					Question 3
12	a correct	0,606011	3			1	c false
13	b correct	0,112266	4			2	d correct
14	c false	0,933697	1			3	a correct
15	d correct	0,927685	2			4	b correct
Sheet1

Cell Formulas
Range	Formula
B12:B15,B7:B10,B2:B5	B2	=RAND()
C2:C5	C2	=RANK.EQ(B2,$B$2:$B$5,0)
G2:G5	G2	=INDEX($A$2:$A$5,MATCH(F2,$C$2:$C$5,0))
C7:C10	C7	=RANK.EQ(B7,$B$7:$B$10,0)
G7:G10	G7	=INDEX($A$7:$A$10,MATCH(F7,$C$7:$C$10,0))
C12:C15	C12	=RANK.EQ(B12,$B$12:$B$15,0)
G12:G15	G12	=INDEX($A$12:$A$15,MATCH(F12,$C$12:$C$15,0))

 

[t0m1noo]

Thank you very much. Is it applicable for 500 questions at once? Or do I have to do it manually?

 

[GraH]

Like I said, requires some set-up and preparation. Doing this took me like 3 minutes (not even, but to give an idea).

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		Question 1	Question 2	Question 3	Question 4	Question 5	Question 6	Question 7	Question 8	Question 9	Question 10	Question 11	Question 12	Question 13	Question 14	Question 15	Question 16	Question 17
2		a	a	a	a	a	a	a	a	a	a	a	a	a	a	a	a	a
3		b	b	b	b	b	b	b	b	b	b	b	b	b	b	b	b	b
4		c	c	c	c	c	c	c	c	c	c	c	c	c	c	c	c	c
5		d	d	d	d	d	d	d	d	d	d	d	d	d	d	d	d	d
6
7	Random	0,851589	0,2122099	0,2842735	0,3731716	0,9133258	0,4938665	0,1197035	0,2889178	0,8699906	0,15201714	0,136959	0,212621	0,813416	0,959053	0,875104	0,402134	0,583288
8		0,5257057	0,8829101	0,787508	0,9756045	0,7972791	0,8843891	0,8322671	0,3380227	0,3653154	0,84079531	0,597778	0,304761	0,724688	0,386263	0,387814	0,237129	0,243142
9		0,7009226	0,1205123	0,4524322	0,5474576	0,8495102	0,7368187	0,3359923	0,9830214	0,7736503	0,85370672	0,66191	0,697999	0,265199	0,060112	0,392862	0,239688	0,28215
10		0,8663001	0,3902849	0,5254654	0,8172733	0,7299182	0,5347639	0,6675355	0,6977805	0,1041614	0,76163027	0,492222	0,389652	0,371742	0,99857	0,50486	0,350256	0,199116
11
12	Rank	2	3	4	4	1	4	4	4	1	4	4	4	1	2	1	1	1
13		4	1	1	1	3	1	1	3	3	2	2	3	2	3	4	4	3
14		3	4	3	3	2	2	3	1	2	1	1	1	4	4	3	3	2
15		1	2	2	2	4	3	2	2	4	3	3	2	3	1	2	2	4
16	Shuffled
17	1	b	c	d	d	a	d	d	d	a	d	d	d	a	b	a	a	a
18	2	d	a	a	a	c	a	a	c	c	b	b	c	b	c	d	d	c
19	3	c	d	c	c	b	b	c	a	b	a	a	a	d	d	c	c	b
20	4	a	b	b	b	d	c	b	b	d	c	c	b	c	a	b	b	d
Sheet1

Cell Formulas
Range	Formula
B7:R10	B7	=RAND()
B12:R15	B12	=RANK(B7,B$7:B$10,0)
B17:R20	B17	=INDEX(B$2:B$5,MATCH(B12,$A$17:$A$20,0))

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Is possible to shuffle answers in multiple choice test in EXCEL?
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[t0m1noo]

Like I said, requires some set-up and preparation. Doing this took me like 3 minutes (not even, but to give an idea).

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		Question 1	Question 2	Question 3	Question 4	Question 5	Question 6	Question 7	Question 8	Question 9	Question 10	Question 11	Question 12	Question 13	Question 14	Question 15	Question 16	Question 17
2		a	a	a	a	a	a	a	a	a	a	a	a	a	a	a	a	a
3		b	b	b	b	b	b	b	b	b	b	b	b	b	b	b	b	b
4		c	c	c	c	c	c	c	c	c	c	c	c	c	c	c	c	c
5		d	d	d	d	d	d	d	d	d	d	d	d	d	d	d	d	d
6
7	Random	0,851589	0,2122099	0,2842735	0,3731716	0,9133258	0,4938665	0,1197035	0,2889178	0,8699906	0,15201714	0,136959	0,212621	0,813416	0,959053	0,875104	0,402134	0,583288
8		0,5257057	0,8829101	0,787508	0,9756045	0,7972791	0,8843891	0,8322671	0,3380227	0,3653154	0,84079531	0,597778	0,304761	0,724688	0,386263	0,387814	0,237129	0,243142
9		0,7009226	0,1205123	0,4524322	0,5474576	0,8495102	0,7368187	0,3359923	0,9830214	0,7736503	0,85370672	0,66191	0,697999	0,265199	0,060112	0,392862	0,239688	0,28215
10		0,8663001	0,3902849	0,5254654	0,8172733	0,7299182	0,5347639	0,6675355	0,6977805	0,1041614	0,76163027	0,492222	0,389652	0,371742	0,99857	0,50486	0,350256	0,199116
11
12	Rank	2	3	4	4	1	4	4	4	1	4	4	4	1	2	1	1	1
13		4	1	1	1	3	1	1	3	3	2	2	3	2	3	4	4	3
14		3	4	3	3	2	2	3	1	2	1	1	1	4	4	3	3	2
15		1	2	2	2	4	3	2	2	4	3	3	2	3	1	2	2	4
16	Shuffled
17	1	b	c	d	d	a	d	d	d	a	d	d	d	a	b	a	a	a
18	2	d	a	a	a	c	a	a	c	c	b	b	c	b	c	d	d	c
19	3	c	d	c	c	b	b	c	a	b	a	a	a	d	d	c	c	b
20	4	a	b	b	b	d	c	b	b	d	c	c	b	c	a	b	b	d
Sheet1

Cell Formulas
Range	Formula
B7:R10	B7	=RAND()
B12:R15	B12	=RANK(B7,B$7:B$10,0)
B17:R20	B17	=INDEX(B$2:B$5,MATCH(B12,$A$17:$A$20,0))

Thanks. I like the first idea. I really appreciate your effort to help me. But I don't know how to apply it for bigger amount of question. I have just basic skills in Excel, so that's why I am not able to do a lot of sets-up and preparation.

 

[mse330]

Try the below macro considering you only have 4 answers per question & they're in column A and shuffled questions will be pasted in column C with the same format you've posted in the attached picture in post #1

Sub ShuffleQuestions()

Dim a As Variant, b As Variant, Ans(1 To 4) As String, Coll As New Collection
a = ActiveSheet.Range("A1").CurrentRegion

ReDim b(1 To UBound(a))
For x = LBound(a) To UBound(a)
    If a(x, 1) Like "*uestion*" Then
        b(x) = a(x, 1)
    ElseIf a(x, 1) Like "a)*" Then
        For y = 0 To 3
            Coll.Add y + 1
            Ans(y + 1) = a(x + y, 1)
        Next
        For y = 0 To 3
            j = WorksheetFunction.RandBetween(1, Coll.Count)
            b(x + y) = Ans(Coll(j))
            Coll.Remove j
        Next
    End If
Next x

ActiveSheet.Range("C1").Resize(UBound(b)) = Application.Transpose(b)

End Sub

 

[GraH]

Thanks. I like the first idea. I really appreciate your effort to help me. But I don't know how to apply it for bigger amount of question. I have just basic skills in Excel, so that's why I am not able to do a lot of sets-up and preparation.

Simply add more Q&A's at the end and drag the formula to the right. That should be do-able even with limited excel knowledge.

 

[t0m1noo]

Try the below macro considering you only have 4 answers per question & they're in column A and shuffled questions will be pasted in column C with the same format you've posted in the attached picture in post #1

Sub ShuffleQuestions()

Dim a As Variant, b As Variant, Ans(1 To 4) As String, Coll As New Collection
a = ActiveSheet.Range("A1").CurrentRegion

ReDim b(1 To UBound(a))
For x = LBound(a) To UBound(a)
    If a(x, 1) Like "*uestion*" Then
        b(x) = a(x, 1)
    ElseIf a(x, 1) Like "a)*" Then
        For y = 0 To 3
            Coll.Add y + 1
            Ans(y + 1) = a(x + y, 1)
        Next
        For y = 0 To 3
            j = WorksheetFunction.RandBetween(1, Coll.Count)
            b(x + y) = Ans(Coll(j))
            Coll.Remove j
        Next
    End If
Next x

ActiveSheet.Range("C1").Resize(UBound(b)) = Application.Transpose(b)

End Sub

Thank you! This macro works perfectly. Is possible to modify this macro in order to there were also questions in column C instead of empty cells?

 

[mse330]

You can simply change the location of the data from column C to any other by changing C1 in the last line of code to D1 or E2 ... etc.