Randomly assigning Data

[Paul K]

Hi, im trying to set up a sheet that will allow me to run a succession of
tournaments and create a rankings table automatically.

Each week we will have a tournament and each week we may not have the same
people attending. I have a list which i will put into excel of all the
people who could possibly turn up.

Basically, what i want this sheet to do, is to assign the people who turn up
on a given week a random position in the fixture sheet for the tournament.
It has to be random so they dont end up drawing the same person each week.

Put simply and out of context, I want to be able to assign a specific number
of cells from a list, into another list of cells in a random order.

Is this possible.

 

[Gary''s Student]

Say we have the complete list of names in C1 thru C40.
In B1 enter a 1 if the person attends, otherwise a zero.

In A1 enter:

=IF(B1=0,-ROW(),RAND()) and copy down

In D1, enter
=IF(LARGE(A$1:A$40,ROW())<0,"",VLOOKUP(LARGE(A$1:A$40,ROW()),A$1:C$40,3,FALSE)) and copy down

Column D will be a randomized list of the attendees. For example:

-1.00000 0 joe1 joe35
0.02104 1 joe2 joe40
-3.00000 0 joe3 joe15
-4.00000 0 joe4 joe11
-5.00000 0 joe5 joe6
0.80728 1 joe6 joe36
-7.00000 0 joe7 joe39
0.54657 1 joe8 joe22
0.08529 1 joe9 joe37
-10.00000 0 joe10 joe21
0.80897 1 joe11 joe8
-12.00000 0 joe12 joe20
0.05786 1 joe13 joe14
0.44981 1 joe14 joe34
0.83528 1 joe15 joe24
-16.00000 0 joe16 joe9
-17.00000 0 joe17 joe13
-18.00000 0 joe18 joe2
-19.00000 0 joe19
0.47453 1 joe20
0.61068 1 joe21
0.72711 1 joe22
-23.00000 0 joe23
0.19358 1 joe24
-25.00000 0 joe25
-26.00000 0 joe26
-27.00000 0 joe27
-28.00000 0 joe28
-29.00000 0 joe29
-30.00000 0 joe30
-31.00000 0 joe31
-32.00000 0 joe32
-33.00000 0 joe33
0.38516 1 joe34
0.89925 1 joe35
0.78968 1 joe36
0.61486 1 joe37
-38.00000 0 joe38
0.76434 1 joe39
0.83609 1 joe40