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

[Millrobin]

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

 

[Rick Rothstein]

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]

or you can try PowerQuery

ID	Questions		ID	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			​	​	​

[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]

.
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.

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]

or you can try PowerQuery

shorter version

[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