Selecting multiple random names from a list, with additional criteria

[neilos81]

Hi all

I wonder if someone could help me. I'm not sure if what I want to do is possible using formulas in Excel but hoping someone can confirm either way.

I have a list of students and their relevant class. For simplicity class in column A and student name in column B. Classes would have codes but could be as simple as Class A through to Class L (And i can easily get a unique list of class codes). I'm wanting a random list of students but with the criteria that it is done by selecting 3 from each class to ensure a spread across all classes.

I've tried by assigning a random number to each student in the class and then using a vlookup for the class code and random number (1-3 for example) but I have the issue that I don't always get pupils assigned with the relevant random number. Is there a way to resolve this issue or is there a better way to do it from the start?

Many thanks in advance.

 

[alansidman]

Rather than have us prepare a spreadsheet with data to show you how to do this, please provide a sample worksheet with several classes and enough students in each to allow for a solution. Use XL2BB to upload your sample so we don't have to recreate it. Help us to help you.

 

[neilos81]

Hi

Thank you. I did try to install the XL2BB when first posting but first had an issue with 'Protected View' and once I'd resolved that in the settings it now won't install due to having macros. I can't find a easy to allow it to install fully with macros. Can you advise?

Many thanks

 

[neilos81]

Book1.xlsx
	A	B	C	D	E	F	G
1	Class Code	Student		Randon Sample:	Class A	Class B	Class C
2	ClassA	Student1
3	ClassA	Student2
4	ClassA	Student3
5	ClassA	Student4
6	ClassA	Student5
7	ClassA	Student6
8	ClassA	Student7
9	ClassA	Student8
10	ClassA	Student9
11	ClassA	Student10
12	ClassA	Student11
13	ClassA	Student12
14	ClassA	Student13
15	ClassA	Student14
16	ClassA	Student15
17	ClassB	Student1
18	ClassB	Student2
19	ClassB	Student3
20	ClassB	Student4
21	ClassB	Student5
22	ClassB	Student6
23	ClassB	Student7
24	ClassB	Student8
25	ClassB	Student9
26	ClassB	Student10
27	ClassB	Student11
28	ClassB	Student12
29	ClassB	Student13
30	ClassB	Student14
31	ClassB	Student15
32	ClassC	Student1
33	ClassC	Student2
34	ClassC	Student3
35	ClassC	Student4
36	ClassC	Student5
37	ClassC	Student6
38	ClassC	Student7
39	ClassC	Student8
40	ClassC	Student9
41	ClassC	Student10
42	ClassC	Student11
43	ClassC	Student12
44	ClassC	Student13
45	ClassC	Student14
46	ClassC	Student15
Sheet1

 

[neilos81]

Table now shown above through XL2BB. In an ideal world, I'd like the 3 random names from each class to appear in the cells highlighted yellow.

 

[alansidman]

In column C =Rand()
Then bring the data into Power Query. The following Mcode is a bit long. Maybe someone else can see a shorter way. After random, cut and paste it to itself so that it doesn't keep refreshing and updating the PQ results.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Class Code"}, {{"Count", each _, type table [Class Code=text, Student=text, Random=number]}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Class Code", "Student", "Random"}, {"Class Code.1", "Student", "Random"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Count",{"Class Code"}),
    #"Split Column by Position" = Table.SplitColumn(#"Removed Columns", "Class Code.1", Splitter.SplitTextByRepeatedLengths(5), {"Class Code.1.1", "Class Code.1.2"}),
    #"Sorted Rows" = Table.Sort(#"Split Column by Position",{{"Random", Order.Ascending}, {"Student", Order.Ascending}, {"Class Code.1.2", Order.Descending}}),
    #"Merged Columns" = Table.CombineColumns(#"Sorted Rows",{"Class Code.1.1", "Class Code.1.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Grouped Rows1" = Table.Group(#"Merged Columns", {"Merged"}, {{"Count", each _, type table [Merged=text, Student=nullable text, Random=nullable number]}}),
    #"Sorted Rows1" = Table.Sort(#"Grouped Rows1",{{"Merged", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows1", "Custom", each Table.AddIndexColumn([Count],"A",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Merged", "Student", "Random", "A"}, {"Merged.1", "Student", "Random", "A"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each [A] < 4),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Merged", "Student"})
in
    #"Removed Other Columns"

 

[Peter_SSs]

I'm not sure if what I want to do is possible using formulas

Are you still using excel 2016 per your profile? If so, try the following. If you do have a newer version, a simpler formula may be available.
I have changed the student names as you had the same students in all 3 classes which I am assuming is not the case.
You also had a space in the class names in E1:G1 but not in column A so I have standardised that as well.
If you want to get a new list of names, just press F9.

24 09 03.xlsm
	A	B	C	D	E	F	G
1	Class Code	Student		Randon Sample:	ClassA	ClassB	ClassC
2	ClassA	Student1			Student12	Student19	Student42
3	ClassA	Student2			Student11	Student20	Student33
4	ClassA	Student3			Student6	Student28	Student45
5	ClassA	Student4
6	ClassA	Student5
7	ClassA	Student6
8	ClassA	Student7
9	ClassA	Student8
10	ClassA	Student9
11	ClassA	Student10
12	ClassA	Student11
13	ClassA	Student12
14	ClassA	Student13
15	ClassA	Student14
16	ClassA	Student15
17	ClassB	Student16
18	ClassB	Student17
19	ClassB	Student18
20	ClassB	Student19
21	ClassB	Student20
22	ClassB	Student21
23	ClassB	Student22
24	ClassB	Student23
25	ClassB	Student24
26	ClassB	Student25
27	ClassB	Student26
28	ClassB	Student27
29	ClassB	Student28
30	ClassB	Student29
31	ClassB	Student30
32	ClassC	Student31
33	ClassC	Student32
34	ClassC	Student33
35	ClassC	Student34
36	ClassC	Student35
37	ClassC	Student36
38	ClassC	Student37
39	ClassC	Student38
40	ClassC	Student39
41	ClassC	Student40
42	ClassC	Student41
43	ClassC	Student42
44	ClassC	Student43
45	ClassC	Student44
46	ClassC	Student45
Random lists

Cell Formulas
Range	Formula
E2:G4	E2	=INDEX($B:$B,AGGREGATE(15,6,ROW($B$2:$B$46)/(($A$2:$A$46=E$1)*ISNA(MATCH($B$2:$B$46,E$1:E1,0))),RANDBETWEEN(1,COUNTIF($A$2:$A$46,E$1)-ROWS(E$1:E1)+1)))

 

[neilos81]

Are you still using excel 2016 per your profile? If so, try the following. If you do have a newer version, a simpler formula may be available.
I have changed the student names as you had the same students in all 3 classes which I am assuming is not the case.
You also had a space in the class names in E1:G1 but not in column A so I have standardised that as well.
If you want to get a new list of names, just press F9.

24 09 03.xlsm
	A	B	C	D	E	F	G
1	Class Code	Student		Randon Sample:	ClassA	ClassB	ClassC
2	ClassA	Student1			Student12	Student19	Student42
3	ClassA	Student2			Student11	Student20	Student33
4	ClassA	Student3			Student6	Student28	Student45
5	ClassA	Student4
6	ClassA	Student5
7	ClassA	Student6
8	ClassA	Student7
9	ClassA	Student8
10	ClassA	Student9
11	ClassA	Student10
12	ClassA	Student11
13	ClassA	Student12
14	ClassA	Student13
15	ClassA	Student14
16	ClassA	Student15
17	ClassB	Student16
18	ClassB	Student17
19	ClassB	Student18
20	ClassB	Student19
21	ClassB	Student20
22	ClassB	Student21
23	ClassB	Student22
24	ClassB	Student23
25	ClassB	Student24
26	ClassB	Student25
27	ClassB	Student26
28	ClassB	Student27
29	ClassB	Student28
30	ClassB	Student29
31	ClassB	Student30
32	ClassC	Student31
33	ClassC	Student32
34	ClassC	Student33
35	ClassC	Student34
36	ClassC	Student35
37	ClassC	Student36
38	ClassC	Student37
39	ClassC	Student38
40	ClassC	Student39
41	ClassC	Student40
42	ClassC	Student41
43	ClassC	Student42
44	ClassC	Student43
45	ClassC	Student44
46	ClassC	Student45
Random lists

Cell Formulas
Range	Formula
E2:G4	E2	=INDEX($B:$B,AGGREGATE(15,6,ROW($B$2:$B$46)/(($A$2:$A$46=E$1)*ISNA(MATCH($B$2:$B$46,E$1:E1,0))),RANDBETWEEN(1,COUNTIF($A$2:$A$46,E$1)-ROWS(E$1:E1)+1)))

Thank you, looks super!! I'm currently on Excel 2021 but assume this will work still?

 

[Peter_SSs]

I'm currently on Excel 2021

Might be worth updating your your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

assume this will work still?

Yes, it would still work but as I mentioned, a simpler formula might be available. Instead of copying that quite long formula down and across to all 9 cells you could use this much shorter formula and just copied across the top 3 cells.

24 09 03.xlsm
	A	B	C	D	E	F	G
1	Class Code	Student		Randon Sample:	ClassA	ClassB	ClassC
2	ClassA	Student1			Student6	Student24	Student39
3	ClassA	Student2			Student10	Student27	Student36
4	ClassA	Student3			Student1	Student17	Student41
5	ClassA	Student4
6	ClassA	Student5
7	ClassA	Student6
8	ClassA	Student7
9	ClassA	Student8
10	ClassA	Student9
11	ClassA	Student10
12	ClassA	Student11
13	ClassA	Student12
14	ClassA	Student13
15	ClassA	Student14
16	ClassA	Student15
17	ClassB	Student16
18	ClassB	Student17
19	ClassB	Student18
20	ClassB	Student19
21	ClassB	Student20
22	ClassB	Student21
23	ClassB	Student22
24	ClassB	Student23
25	ClassB	Student24
26	ClassB	Student25
27	ClassB	Student26
28	ClassB	Student27
29	ClassB	Student28
30	ClassB	Student29
31	ClassB	Student30
32	ClassC	Student31
33	ClassC	Student32
34	ClassC	Student33
35	ClassC	Student34
36	ClassC	Student35
37	ClassC	Student36
38	ClassC	Student37
39	ClassC	Student38
40	ClassC	Student39
41	ClassC	Student40
42	ClassC	Student41
43	ClassC	Student42
44	ClassC	Student43
45	ClassC	Student44
46	ClassC	Student45
Random lists 2021

Cell Formulas
Range	Formula
E2:G4	E2	=LET(f,FILTER($B$2:$B$46,$A$2:$A$46=E1),INDEX(SORTBY(f,RANDARRAY(ROWS(f))),{1;2;3}))
Dynamic array formulas.

 

[neilos81]

Thank you so much, this is fantastic! Is there any simple way for me to understand what each part of the formula does? Always on the look out to educate myself where Excel is concerned...!