Select random name from classroom roster without repeating?

[WHITERL]

Hi,

Middle school teacher here. I'd like to create a way to hit F9 to select a student at random from my classroom roster. Currently I am typing in and selecting the row of names and changing the formula every hour to just select a student at random, but it does repeat often.

Is there a way to set up the spreadsheet so I can select one of 5 class hours, and then it allows me to return a random student from that hour?

My current function is this: INDEX(E2:E30,RANDBETWEEN(1,29)) so I have to change the column each hour, and then the randbetween numbers as well.

Below I tried to set up a blue print of what I'm after
[TABLE="width: 500"]
<tbody>[TR]
[TD]1st hour[/TD]
[TD]2nd hour[/TD]
[TD]3rd hour[/TD]
[TD]4th hour[/TD]
[TD]5th hour[/TD]
[/TR]
[TR]
[TD]student 1[/TD]
[TD]student 1[/TD]
[TD]student 1[/TD]
[TD]student 1[/TD]
[TD]student 1[/TD]
[/TR]
[TR]
[TD]student 2[/TD]
[TD]student 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Select hour[/TD]
[TD](choices 1,2,3,4,5)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Random student form hour select[/TD]
[TD](Name of random student)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Try

=INDEX(INDEX($A$2:$E$30,0,MATCH(G2&"*",$A$1:$E$1,0)),RANDBETWEEN(1,29))

where G2 contains the choice (1 or 2 or 3 or 4 or 5)

M.

 

[Eric W]

If you want to do it without repeating, you need to keep track of the students already picked. Something like this:

Excel 2012

	D	E	F	G	H	I	J	K	L	M	N	O	P
Hour	1st Hour	2nd Hour	3rd Hour	4th Hour	5th Hour	Hour
Amy	Ed	Hal	Mary	Sam	Students:	Ken	Students:	Ken
Bob	Felice	Iris	Nicole	Tay	Iris	Jacque
Cal	Greg	Jacque	Oscar	Ursula	Larry	Larry
Debbie	Ken	Paul	Vikki	Hal	Iris
Larry	Quincy	Jacque	Hal
Rae

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]0.800136[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.93636[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.307902[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.963657[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.979513[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O2[/TH]
[TD="align: left"]=IF(P2="","",INDEX($E$2:$I$30,RANK(P2,$P$2:$P$30),MATCH($L$1&"*",$E$1:$I$1,0)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]P2[/TH]
[TD="align: left"]=IF(ROWS($P$2:$P2)<=COUNTA(INDEX($E$2:$I$30,0,MATCH($L$1&"*",$E$1:$I$1,0))),RAND(),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L2[/TH]
[TD="align: left"]{=IFERROR(INDEX($E$2:$I$30,SMALL(IF((COUNTIF($L$1:$L1,INDEX($E$2:$I$30,0,MATCH($L$1&"*",$E$1:$I$1,0)))=0)*(INDEX($E$2:$I$30,0,MATCH($L$1&"*",$E$1:$I$1,0))<>""),ROW($E$2:$E$30)-ROW($E$2)+1),RANDBETWEEN(1,COUNTA(INDEX($E$2:$I$30,0,MATCH($L$1&"*",$E$1:$I$1,0)))-ROWS($L$2:$L2)+1)),MATCH($L$1&"*",$E$1:$I$1,0)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]




The L2 formula is an array formula, copy the formula to your output cell, change the references to match your sheet, then confirm with Control+Shift+Enter. Then drag down the column as needed. The formula is a bit messy!

If you're willing to use a helper column, the O and P formulas are much shorter, not array formulas, but you need to drag them down 29 rows (at least as large as your biggest hour).