Random Timetable Generation

[Guest]

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?

 

[R.VENKATARAMAN]

I have simplified the problem
a1 is "name" and B1 is "index no."
a2 to a16 contains the names
b2 to B16 contains numbers 1,2,3, in serial upto 15

in E5 I type
=randbetween(1,15)
I get some number betwen 1 and 15
I drag e5 upto G9
as the random numbers change if I do something in the sheet
I copy the range e5 to g9 and pastespecial -values to the same place
if there are repetitive numbers in E5 to G9 I change it some other number



now I go to E13 and type
=INDEX($A$1:$A$16,MATCH(E5,$B$1:$B$16,0)) and hit enter
I drag E13 to G17

I get random names.

change to suit your needs.

may not be an elegant solution but you get what you want.

 

[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

 

[Guest]

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