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.

 

[Peter_SSs]

You're welcome.

Is there any simple way for me to understand what each part of the formula does?

=LET(f,FILTER($B$2:$B$46,$A$2:$A$46=E1),INDEX(SORTBY(f,RANDARRAY(ROWS(f))),{1;2;3}))

LET(f,FILTER($B$2:$B$46,$A$2:$A$46=E1)
f stores a list of the names for the class whose name is in row 1

RANDARRAY(ROWS(f))
creates a set of random numbers with the same number of values as members of the class

SORTBY(f,RANDARRAY(ROWS(f)))
sorts the class list using the random numbers. That is, this jumbles the class list.

INDEX(SORTBY(f,RANDARRAY(ROWS(f))),{1;2;3})
takes the first 3 names from the jumbled class list