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

t0m1noo

New Member
Joined
Apr 17, 2020
Messages
7
Office Version
2019
Platform
Windows
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:
example1.png

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

Thanks for the help.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

GraH

Active Member
Joined
Mar 22, 2020
Messages
485
Office Version
365, 2016
Platform
Windows
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
ABCDEFG
1Question 1randomrankQuestion 1
2a correct
0,590911
2
1
c correct
3b false
0,120755
4
2
a correct
4c correct
0,814065
1
3
d false
5d false
0,227136
3
4
b false
6Question 2Question 2
7a false
0,155751
3
1
d false
8b correct
0,311927
2
2
b correct
9c false
0,0893
4
3
a false
10d false
0,487708
1
4
c false
11Question 3Question 3
12a correct
0,606011
3
1
c false
13b correct
0,112266
4
2
d correct
14c false
0,933697
1
3
a correct
15d correct
0,927685
2
4
b correct
Sheet1
Cell Formulas
RangeFormula
B12:B15,B7:B10,B2:B5B2=RAND()
C2:C5C2=RANK.EQ(B2,$B$2:$B$5,0)
G2:G5G2=INDEX($A$2:$A$5,MATCH(F2,$C$2:$C$5,0))
C7:C10C7=RANK.EQ(B7,$B$7:$B$10,0)
G7:G10G7=INDEX($A$7:$A$10,MATCH(F7,$C$7:$C$10,0))
C12:C15C12=RANK.EQ(B12,$B$12:$B$15,0)
G12:G15G12=INDEX($A$12:$A$15,MATCH(F12,$C$12:$C$15,0))
 

t0m1noo

New Member
Joined
Apr 17, 2020
Messages
7
Office Version
2019
Platform
Windows
Thank you very much. Is it applicable for 500 questions at once? Or do I have to do it manually?
 

GraH

Active Member
Joined
Mar 22, 2020
Messages
485
Office Version
365, 2016
Platform
Windows
Like I said, requires some set-up and preparation. Doing this took me like 3 minutes (not even, but to give an idea).
Cell Formulas
RangeFormula
B7:R10B7=RAND()
B12:R15B12=RANK(B7,B$7:B$10,0)
B17:R20B17=INDEX(B$2:B$5,MATCH(B12,$A$17:$A$20,0))
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,758
Office Version
365
Platform
Windows
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

New Member
Joined
Apr 17, 2020
Messages
7
Office Version
2019
Platform
Windows
Like I said, requires some set-up and preparation. Doing this took me like 3 minutes (not even, but to give an idea).
Cell Formulas
RangeFormula
B7:R10B7=RAND()
B12:R15B12=RANK(B7,B$7:B$10,0)
B17:R20B17=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

Well-known Member
Joined
Oct 18, 2007
Messages
652
Office Version
365
Platform
Windows
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

VBA Code:
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

Active Member
Joined
Mar 22, 2020
Messages
485
Office Version
365, 2016
Platform
Windows
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

New Member
Joined
Apr 17, 2020
Messages
7
Office Version
2019
Platform
Windows
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

VBA Code:
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

Well-known Member
Joined
Oct 18, 2007
Messages
652
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,964
Messages
5,471,735
Members
406,780
Latest member
Todd Gentry

This Week's Hot Topics

Top