Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / August 2007

Tip: Looking for answers? Try searching our database.

generate randomly assigned lists

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
jt mwc - 06 Aug 2007 23:58 GMT
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?
joeu2004 - 07 Aug 2007 00:25 GMT
> 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?

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.)
Gary''s Student - 07 Aug 2007 00:28 GMT
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.
Signature

Gary''s Student - gsnu200735

> 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?
Max - 07 Aug 2007 03:16 GMT
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 ..
Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> 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?
Max - 07 Aug 2007 11:56 GMT
> http://www.flypicture.com/download/MTE1OTM=

In case you're having difficulties with the above link, just do a copy n
paste of the entire link (inclusive of the "=" at the end) into your
browser's address bar, press ENTER. That should give you the correct d/l
button at flypicture.
Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

jt mwc - 08 Aug 2007 16:32 GMT
Thanks so much this is exactly what I was looking for!

> One way which delivers exactly what you're after ..
>
[quoted text clipped - 23 lines]
> > etc.  Needs to be completely random assignments.  Is there a quick way to do
> > this with excel 2003?
Max - 08 Aug 2007 19:19 GMT
Welcome. Great to hear that.
Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Thanks so much this is exactly what I was looking for!
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.