How can I randomly sort a column of data using Excel?

[Paul F.]

A bowling league consisting of an equal number of men and women would like to
do a blind draw of two person teams (one male & one female) randomly each
night of play so that the teams members will be different each week and
randomly selected.

 

[StumpedAgain]

Here's a quick work around:

Have a column in between the men and women names and on the right side of
each column have =rand(). You can then sort (Highlight men and their
randomly assigned number, go to Data->Sort and then do the same for the
women) in ascending order and the two with the lowest number be on the same
team all the way to the two with the highest number. It's a little more
manual, but it's random and pretty simple! Also, to do this next week and
not have to re-type =rand() simply highlight the cells and press "F9". Enjoy
bowling!

 

[Chip Pearson]

You don't necessarily need to use a sort. Suppose your list of men are in
A1:A5 and the list of women are in B1:B5. The formula

=OFFSET(A1,RAND()*4,0,1,1)

will return a randomly selected male. The formula

=OFFSET(B1,RAND()*4,0,1,1)

will return a randomly selected female.

In both formulas, change the '4' to the number of rows of names, minus 1.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)