Format multiple choice test questions with 4 possible answers

[Millrobin]

I would like to format 200 multiple choice questions with a-d possible answers. I then will randomize 52 questions for each 20 students.

I have tried importing question bank from Word 365 to Excel, but a time consuming editing process is required. So if I type in the questions in Excel, how do I format them so they will print correctly after random selections are made using RAND function?

Thanks everyone for your time and assistance.

 

[Col Delane]

The text formatting of Word is far superior to that of Excel (which does not handle indenting or bullets very well at all), so consider sticking with Word but with some adaptations.

Here's my not-so-difficult (and not fully automated) solution.

Insert a Table in a Word document with three columns & 201 rows

Columns, with these headings in row 1:
​

• 
  [*=1]"No."
  [*=1]"Select?"
  [*=1]"Question"

Arrange each question (& its possible answers) in a separate cell of the Question column.
​

In a sheet in an Excel macro-enabled workbook (*.xlsm):

1. A1 = "No."
2. B1 = "Selected?"
3. A2:A201 = sequential numbers 1-200
4. Format A1:B201 with horizontal alignment = Center
5. Select the range A1:B201
6. Apply the Autofilter to the range

Open the Visual Basic Editor (VBE) via Alt+F11
Add the following code to a standard module (e.g. Module1)

Sub QuestionNoPicker()
'Adapted from https://stackoverflow.com/questions/35921217/excel-vba-to-get-random-integer-values-without-repetitions

    Dim i As Integer, num As Integer
    Dim rng As Range
    Dim picked As Object 'Scripting.Dictionary
    Dim val As Variant

    'Clear the selection range of any previous entries
    Range("B2:B201").ClearContents    
    
    'Populate the sheet with values 1:200 in range A1:A200
    Set rng = Range("A2:A201")
    For i = 1 To 200
        rng.Cells(i) = i
    Next

    'Store which numbers have been already chosen
    Set picked = CreateObject("Scripting.Dictionary")

    'Select 52 random numbers:
    i = 1
    While picked.Count < 52
        num = Application.WorksheetFunction.RandBetween(1, 200)
        If Not picked.Exists(num) Then
            picked.Add num, i
            i = i + 1
        End If
    Wend

    'Now, identify those numbers on the sheet
    For Each val In picked.Keys()
        rng.Cells(val).Offset(0, 1).Value = "Y"
    Next
   
End Sub

Back in Excel:
Either:

1. add a Form Control button, or a Shape, to the sheet, and link the macro to it, or
2. Customize your Quick Access Toolbar (for this workbook only) by adding the macro to the QAT.

Save your workbook.

Run the macro
Select "Y" in the autofilter applied to the "Selected?" column to show only the questions randomly selected by the macro.

Back in Word:

1. Once you have the 52 numbers, enter a flag (e.g. "Y") in the "Select?" column of your Word Table next to the matching question number.
2. Select all of the Word Table then Sort it in Z-A alphabetical order by the "Select?" column to locate all your selected questions at the top (or in A-Z order if you want them at the bottom).
3. Print/copy the selected questions.

 

[Millrobin]

I will try your option and let you know if I can make it work. Many thanks!

 

[Col Delane]

Here's my version of the spreadsheet (to Qtn No. 10 only) and the code (modified since I originally posted it @ #2 ):

Whilst it doesn't show here (the formatting doesn't copy properly), A8:B208 form an Excel Table named "tblRng".

The following table shows the Defined Names created to make it easier to refer to ranges in the VBA code.

Name	RefersTo Range
QtnNos	= tblRng[Question No.]
RandSelection	= tblRng[Select?]
TotalNoQtns	='.'!$C$3
RandomQtnsNo	='.'!$C$4

<tbody>
</tbody>

Sheet: "."

<tbody>
</tbody>

Formulas:
A7 = SUBTOTAL( 4, QtnNos )
B7 = SUBTOTAL( 3, RandSelection )

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​	Random Question Selector
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​	Total No. of questions in population:		200 ​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​	No. of questions to randomly select:		52​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​	Counts:​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​	200 ​	52 ​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​	Question No. ​	Select?​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​	1 ​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​	2 ​	Y​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​	3 ​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​	4 ​	Y​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR]​	5 ​	Y​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR]​	6 ​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]15[/COLOR]​	7 ​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR]​	8 ​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]17[/COLOR]​	9 ​	Y​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]18[/COLOR]​	10 ​

<tbody>
</tbody>

Sheet: "."

<tbody>
</tbody>

I inserted/added a Form Control button with the label "Refresh Selection" to A5:B5 and linked it to the macro below so the user can just click the button from the spreadsheet rather than open the VBE or run the macro from the Developer tab.

VBA Code (in standard module "Module1"):

Sub RandomNoPicker()

' Author:         David Zemens, Stackoverflow.com user
' Co-Author:    Colin Delane, CA, Financial Analyst, Perth, Western Australia
' Source:       Code adapted from  https://stackoverflow.com/questions/35921217/excel-vba-to-get-random-integer-values-without-repetitions
' Purpose:      Solution to issue raised by Millrobin on MrExcel Forum  27/02/2019 (to pick 52 random numbers in range from 1-200)
                     'https://www.mrexcel.com/forum/excel-questions/1089062-format-multiple-choice-test-questions-4-possible-answers.html#post5235145
' Pre-requisites:
' WARNINGS:
'---------------------------------------------------------------------------------------

    'Declare Procedure Variables
    Dim i As Integer, num As Integer
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range
    Dim rng4 As Range
    Dim picked As Object 'Scripting.Dictionary
    Dim val As Variant
    
    Set rng1 = Range("TotalNoQtns")
    Set rng2 = Range("RandomQtnsNo")
    Set rng3 = Range("QtnNos")
    Set rng4 = Range("RandSelection")
    
    'Clear the ranges of any previous entries
    rng3.ClearContents
    rng4.ClearContents
    
    'Populate the sheet with question numbers
    For i = 1 To rng1.Value
        rng3.Cells(i) = i
    Next

    'Store which numbers have been already chosen
    Set picked = CreateObject("Scripting.Dictionary")

    'Select random numbers:
    i = 1
    While picked.Count < rng2.Value
        num = Application.WorksheetFunction.RandBetween(1, rng1.Value)
        If Not picked.Exists(num) Then
            picked.Add num, i
            i = i + 1
        End If
    Wend

    'Now, identify those numbers on the sheet
    For Each val In picked.Keys()
        rng3.Cells(val).Offset(0, 1).Value = "Y"
    Next
   
   MsgBox "Done!"
   
End Sub