Random sort a number of test questions from question pool in Excel w/o VBA

Millrobin

New Member
Joined
Feb 23, 2019
Messages
9
Is there a for me (non literate Excel user) to extract a number of questions from a pool of test questions in Excel?

The questions have in the first column numbered 1-200

I would like to pick random questions, say 52, for each person from that pool of questions.

Then the next person would get a different set of 52 questions.

I have gotten as far as a RAND formula but I don't know where or how to insert it on a new sheet.

I hope someone can help me. Many thanks
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,691
Office Version
  1. 2010
Platform
  1. Windows
With your questions in Column A starting at Row 1, put this formula in cell B1 and copy it down to the bottom of your questions list...

=RAND()

Now select Columns A and B and then click the Sort button (Data tab, Sort & Filter panel), under Column select Column B and click OK. Your list in Column A is now randomly ordered. Select the first 52 rows for person 1, then next 52 questions for person 2, and so on.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
or you can try PowerQuery

IDQuestionsID
Questions
ID
Questions
1​
Q 001
109​
Q 109​
149​
Q 149​
2​
Q 002
77​
Q 077​
155​
Q 155​
3​
Q 003
120​
Q 120​
24​
Q 024​
4​
Q 004
156​
Q 156​
154​
Q 154​
5​
Q 005
134​
Q 134​
26​
Q 026​
6​
Q 006
87​
Q 087​
109​
Q 109​
7​
Q 007
153​
Q 153​
27​
Q 027​
8​
Q 008
182​
Q 182​
193​
Q 193​
9​
Q 009
12​
Q 012​
38​
Q 038​
10​
Q 010
38​
Q 038​
23​
Q 023​
11​
Q 011
160​
Q 160​
84​
Q 084​
12​
Q 012
122​
Q 122​
115​
Q 115​
13​
Q 013
97​
Q 097​
148​
Q 148​
14​
Q 014
173​
Q 173​
124​
Q 124​
15​
Q 015
123​
Q 123​
80​
Q 080​
16​
Q 016
94​
Q 094​
78​
Q 078​
17​
Q 017
115​
Q 115​
7​
Q 007​
18​
Q 018
75​
Q 075​
153​
Q 153​
19​
Q 019
197​
Q 197​
3​
Q 003​
20​
Q 020
125​
Q 125​
189​
Q 189​
21​
Q 021
81​
Q 081​
91​
Q 091​
22​
Q 022
55​
Q 055​
105​
Q 105​
23​
Q 023
187​
Q 187​
130​
Q 130​
24​
Q 024
180​
Q 180​
107​
Q 107​
25​
Q 025
66​
Q 066​
64​
Q 064​
26​
Q 026
29​
Q 029​
165​
Q 165​
27​
Q 027
119​
Q 119​
117​
Q 117​
28​
Q 028
98​
Q 098​
90​
Q 090​
29​
Q 029
39​
Q 039​
41​
Q 041​
30​
Q 030
106​
Q 106​
183​
Q 183​
31​
Q 031
144​
Q 144​
157​
Q 157​
32​
Q 032
107​
Q 107​
125​
Q 125​
33​
Q 033
64​
Q 064​
50​
Q 050​
34​
Q 034
147​
Q 147​
87​
Q 087​
35​
Q 035
143​
Q 143​
172​
Q 172​
36​
Q 036
23​
Q 023​
49​
Q 049​
37​
Q 037
41​
Q 041​
68​
Q 068​
38​
Q 038
51​
Q 051​
104​
Q 104​
39​
Q 039
128​
Q 128​
39​
Q 039​
40​
Q 040
1​
Q 001​
100​
Q 100​
41​
Q 041
20​
Q 020​
142​
Q 142​
42​
Q 042
79​
Q 079​
59​
Q 059​
43​
Q 043
100​
Q 100​
29​
Q 029​
44​
Q 044
27​
Q 027​
134​
Q 134​
45​
Q 045
161​
Q 161​
17​
Q 017​
46​
Q 046
62​
Q 062​
47​
Q 047​
47​
Q 047
45​
Q 045​
76​
Q 076​
48​
Q 048
172​
Q 172​
120​
Q 120​
49​
Q 049
111​
Q 111​
101​
Q 101​
50​
Q 050
127​
Q 127​
53​
Q 053​
51​
Q 051
89​
Q 089​
94​
Q 094​
52​
Q 052
188​
Q 188​
139​
Q 139​
53​
Q 053
54​
Q 054
55​
Q 055
56​
Q 056
57​
Q 057
58​
Q 058
59​
Q 059
60​
Q 060
61​
Q 061
62​
Q 062
63​
Q 063
64​
Q 064
65​
Q 065
66​
Q 066
67​
Q 067
68​
Q 068
69​
Q 069
70​
Q 070
71​
Q 071
72​
Q 072
73​
Q 073
74​
Q 074
75​
Q 075
76​
Q 076
77​
Q 077
78​
Q 078
79​
Q 079
80​
Q 080
81​
Q 081
82​
Q 082
83​
Q 083
84​
Q 084
85​
Q 085
86​
Q 086
87​
Q 087
88​
Q 088
89​
Q 089
90​
Q 090
91​
Q 091
92​
Q 092
93​
Q 093
94​
Q 094
95​
Q 095
96​
Q 096
97​
Q 097
98​
Q 098
99​
Q 099
100​
Q 100
101​
Q 101
102​
Q 102
103​
Q 103
104​
Q 104
105​
Q 105
106​
Q 106
107​
Q 107
108​
Q 108
109​
Q 109
110​
Q 110
111​
Q 111
112​
Q 112
113​
Q 113
114​
Q 114
115​
Q 115
116​
Q 116
117​
Q 117
118​
Q 118
119​
Q 119
120​
Q 120
121​
Q 121
122​
Q 122
123​
Q 123
124​
Q 124
125​
Q 125
126​
Q 126
127​
Q 127
128​
Q 128
129​
Q 129
130​
Q 130
131​
Q 131
132​
Q 132
133​
Q 133
134​
Q 134
135​
Q 135
136​
Q 136
137​
Q 137
138​
Q 138
139​
Q 139
140​
Q 140
141​
Q 141
142​
Q 142
143​
Q 143
144​
Q 144
145​
Q 145
146​
Q 146
147​
Q 147
148​
Q 148
149​
Q 149
150​
Q 150
151​
Q 151
152​
Q 152
153​
Q 153
154​
Q 154
155​
Q 155
156​
Q 156
157​
Q 157
158​
Q 158
159​
Q 159
160​
Q 160
161​
Q 161
162​
Q 162
163​
Q 163
164​
Q 164
165​
Q 165
166​
Q 166
167​
Q 167
168​
Q 168
169​
Q 169
170​
Q 170
171​
Q 171
172​
Q 172
173​
Q 173
174​
Q 174
175​
Q 175
176​
Q 176
177​
Q 177
178​
Q 178
179​
Q 179
180​
Q 180
181​
Q 181
182​
Q 182
183​
Q 183
184​
Q 184
185​
Q 185
186​
Q 186
187​
Q 187
188​
Q 188
189​
Q 189
190​
Q 190
191​
Q 191
192​
Q 192
193​
Q 193
194​
Q 194
195​
Q 195
196​
Q 196
197​
Q 197
198​
Q 198
199​
Q 199
200​
Q 200

Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(),
    RawData = Table.PromoteHeaders(Source{[Name="tblQ"]}[Content],[PromoteAllScalars=true]),
    List = {1..200},
    #"Converted to Table" = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "ID"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Rnd", each List.Random(1)),
    #"Expanded Rnd" = Table.ExpandListColumn(#"Added Custom", "Rnd"),
    Mrg = Table.NestedJoin(RawData,{"ID"},#"Expanded Rnd",{"ID"},"ID#",JoinKind.LeftOuter),
    #"Expanded ID#" = Table.ExpandTableColumn(Mrg, "ID#", {"ID", "Rnd"}, {"ID#.ID", "ID#.Rnd"}),
    #"Sorted Rows" = Table.Sort(#"Expanded ID#",{{"ID#.Rnd", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"ID#.ID", "ID#.Rnd"}),
    #"Kept First Rows" = Table.FirstN(#"Removed Columns",52)
in
    #"Kept First Rows"[/SIZE]

after use Ctrl+Alt+F5 you'll get new numbers and questions
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,856
.
Another approach :

This is just some of the code involved. The workbook link will reveal the remainder.

Instead of STUDENT NAMES you can replace those with the Questions.

Code:
Option Explicit


Sub PickNamesAtRandom()


Dim HowMany As Integer
Dim NoOfNames As Long
Dim RandomNumber As Integer
Dim Names() As String 'Array to store randomly selected names
Dim i As Byte
Dim CellsOut As Long 'Variable to be used when entering names onto worksheet
Dim ArI As Byte 'Variable to increment through array indexes


Application.ScreenUpdating = False


HowMany = Range("D3").Value
CellsOut = 6


ReDim Names(1 To HowMany) 'Set the array size to how many names required
NoOfNames = Application.CountA(Range("A:A")) - 1 ' Find how many names in the list
i = 1


Do While i <= HowMany
RandomNo:
    RandomNumber = Application.RandBetween(2, NoOfNames + 1)
    'Check to see if the name has already been picked
    For ArI = LBound(Names) To UBound(Names)
        If Names(ArI) = Cells(RandomNumber, 1).Value Then
            GoTo RandomNo
        End If
    Next ArI
    Names(i) = Cells(RandomNumber, 1).Value ' Assign random name to the array
    i = i + 1
Loop


'Loop through the array and enter names onto the worksheet
For ArI = LBound(Names) To UBound(Names)


    Cells(CellsOut, 4) = Names(ArI)
    CellsOut = CellsOut + 1


Next ArI


Application.ScreenUpdating = True


End Sub


Download Workbook : https://www.amazon.com/clouddrive/share/7sJ8qd93sBzHYgxvIETG8PEIlmcH36WfyxNNacHxvYM
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
or you can try PowerQuery

shorter version

Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(),
    RawData = Table.PromoteHeaders(Source{[Name="tblQ"]}[Content],[PromoteAllScalars=true]),
    Random = Table.Sort(Table.ExpandListColumn(Table.AddColumn(RawData, "Rnd", each List.Random(1)), "Rnd"),{{"Rnd", Order.Ascending}}),
    Kept52 = Table.FirstN(Table.RemoveColumns(Random,{"Rnd"}),52)
in
    Kept52[/SIZE]

after use Ctrl+Alt+F5 you'll get new numbers and questions
 

Watch MrExcel Video

Forum statistics

Threads
1,122,827
Messages
5,598,329
Members
414,229
Latest member
Josu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top