Random Timetable Generation

[Boris]

I'm trying to set up a timetable (4 columns x 10 rows) that draws names at
random from a list of 40 given names in a separate column. Any ideas how to
get it to non-repeat the fill in?

 

[Ragdyer]

Probably the least complicated way would be to place your names in a list in
an "out of the way" column, say Z1:Z40
In Y1, enter this:
=RAND()
And drag down to copy to Y40.

Now, say your timetable was in A1: D10.
In A1, B1, C1 and D1, enter,
=Z1, =Z11, =Z21, =Z31
And select all 4 cells and drag down to copy to row 10.

All you do now is select Y1:Z40. and sort on ColumnY, and you have your
random, non-repeating timetable list.
Just resort to change the timetable to a new random list.

 

[vandenberg p]

Try this:

Assume that the 40 names are in col A starting in row two.
Also assume that the 10 name that you want are in col C starting in row
two.
In column B, starting in row 2 put the =Rand() and copy it for forty rows
In Col C row 2 put =A2 and copy for 10 rows

Now sort the columns A and B as a block but sort on Column B.

This will give you the names sorted in random order and the first 10 names
will be in column C (In fact you could use any sequence of 10 from the
list of 40).

If you want to re-sample simply sort the data again. You will get a new
sample

Here is the way the output looks (I used names of a1, a2, a3, etc to
start with).

Name Random The Ten
a17 0.1163683 a17
a26 0.462322897 a26
a20 0.547955588 a20
a01 0.684784865 a01
a23 0.38311757 a23
a27 0.516609698 a27
a09 0.002107826 a09
a05 0.147075769 a05
a15 0.175027385 a15
a30 0.990084821 a30
a31 0.290463193
a08 0.522376428
a22 0.978672396
a21 0.361389591
a12 0.221038725
a38 0.743687859
etc etc






: I'm trying to set up a timetable (4 columns x 10 rows) that draws names at
: random from a list of 40 given names in a separate column. Any ideas how to
: get it to non-repeat the fill in?

 

[Max]

Another play to try ..

Assuming the names are placed in A1:A40

Put in B1: =RAND()
Copy down to B40

Put in C1:
=INDEX(A:A,MATCH(SMALL(B:B,ROWS($A$1:A1)),B:B,0))
Copy down to C40

This will return a random, non repeating shuffle
of the list of names in A1:A40

(Tapping F9 will recalc and re-generate afresh)

Now you could just hide away cols A and B,
use col C as the first col in the timetable and
build the rest of the timetable in the adjacent cols

If and when required, just freeze
the timetable results elsewhere
with a copy > paste special > values > OK

 

[Max]

Put in C1:

=INDEX(A:A,MATCH(SMALL(B:B,ROWS($A$1:A1)),B:B,0))
Copy down to C40

Correction, since your timetable only has 10 rows:

Copy down to C40

should read as

Copy down to C10

In C1:C10 will be returned 10 random, non repeating
names from the list in A1:A40

(If you need more than 10, just copy down more rows as desired,
or all the way up to C40 to get the full shuffle of the list in A1:A40)

 

[Bernd Plumhoff]

I suggest to take my function UniqRandInt() from
http://www.sulprobil.com/html/uniqrandint.html
then select your 10 rows and 4 columns and enter
=INDEX(Sheet2!A1:A40,UniqRandInt(40,FALSE))
as array formula (press CTRL+ALT+RETURN and not only
RETURN to enter)

If you want your names to change with each new calculation
then change FALSE to TRUE.

HTH,
Bernd

 

[Boris]

Hi Bernd,
Thanks for the response, this initially worked but after saving my work and
going back in I kept getting a #NAME? error whenever I tried to use it. It's
like the function isn't sticking in the VBA, I checked this and it is there
though. When it does work though, it is a beautiful thing.

Cheers
Boris