Random Selection

[Erika]

I have my 30 students listed in a spreadsheet, I would like to find a way in
excel that I can set up a formula that will randomly pair them up in groups
of 2.

I have them listed like the example below

1 Person A
2 Person B
3 Person C
etc.
I would like the result of the formula to random pair up the students.

 

[Elkar]

One way:

With Student names in Column A, enter this formula in Column B:

=RAND()

Copy down for each student.

Then sort Columns A & B by Column B. Your student names will now be in a
new random order. The names in row 1 & 2 would be your first group, row 3 &
4 would be your second group, etc....

Just perform the Sort again to re-randomize the names.

HTH
Elkar

 

[joeu2004]

I have my 30 students listed in a spreadsheet, I would like to find a
way in excel that I can set up a formula that will randomly pair them
up in groups of 2.  I have them listed like the example below
1  Person A
2  Person B
3  Person C
etc.

Put =RAND() into a column of 30 cells starting in row 2, e.g. Z2:Z31.

Note: If you do not want your random selection changing everytime you
modify any(!) cell in the worksheet, copy-and-paste-special-value
Z2:Z31 into another range, e.g. Y2:Y31, and substitute "Y" for "Z"
below. (Or you could copy-and-paste-special-value Z2:Z31 over Z2:Z31
if this a one-time thing.)

Suppose your list above is in A1:A30, and you want your pairwise list
in B1:C1 through B15:C15. Enter the following formulas into B1 and C1
and copy down through B15 and C15:

B1: =INDEX($A$1:$A$30,RANK(OFFSET($Z$1,2*ROW(B1)-1,0),$Z$2:$Z$31))
C1: =INDEX($A$1:$A$30,RANK(OFFSET($Z$1,2*ROW(B1),0),$Z$2:$Z$31))