generate randomly assigned lists

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a list of 36 names, we would like to be able to generate lists of
randomly assigned groups such as 3 groups of 12 names or 4 groups of 9 names,
etc. Needs to be completely random assignments. Is there a quick way to do
this with excel 2003?
 
I have a list of 36 names, we would like to be able to generate lists of
randomly assigned groups such as 3 groups of 12 names or 4 groups
of 9 names, etc. Needs to be completely random assignments. Is
there a quick way to do this with excel 2003?
Click to expand...

As a manual process, put =RAND() into the 36 cells of a column (or
row) adjacent to your data, select both columns (or rows), and click
on Data >> Sort to sort based on the column with RAND(). (Note: The
act of sorting will change all the values of the RAND() cells. No
matter; you don't really care what those values are.)

That randomizes the entire list of 36 names. Now, simply break them
into groups of whatever size you wish.

(You can also delete the column or row that contains the RAND()
values.)
 
Put the names in column A and =RAND() in column B.

Sort both columns by column B. Then just pick off the first three or the
first fourm etc.
 
One way which delivers exactly what you're after ..

Illustrated in this template:
http://www.flypicture.com/download/MTE1OTM=
Random assign 36 names to 4x9 n 3x12.xls

Source items assumed listed in A1:A36
In B1: =RAND()
In C1: =INDEX(A$1:A$36,RANK(B1,B$1:B$36))
Select B1:C1, copy down to C36

Now, set it up to extract from the randomized list in C1:C36
for the 2 desired groupings

Random 3 groups of 12 names
In F2: =OFFSET($C$1,ROW(A1)*12-12+COLUMN(A1)-1,)
Copy F2 across by 12 cols to Q2, fill down by 3 rows to Q4

Random 4 groups of 9 names
In F7: =OFFSET($C$1,ROW(A1)*9-9+COLUMN(A1)-1,)
Copy F7 across by 9 cols to N7, fill down by 4 rows to N10

Press F9 to regenerate ..
 
Back
Top