Creating a randomized class schedule

[scraffat]

Here's a brain teaser (that I can't come up with a solution for:

I have 60 children that each need to take 4 classes in a day.

There are 4 time slots for the classes, and 15 slots in each class. I don't want the same students in each class, would like to randomize it a bit.

Any ideas??

 

[Toadstool]

Hi Scraffat,

The obvious solution would be to use one of the RAND functions but the challenge is that if you make a change with causes it to recalculate then a new set of random numbers is generated and so your results change. You could generate random numbers then copy&paste as values to fix them but I wanted to avoid a manual task and you'd still have to handle duplicate results.

I'm just showing a part of each table for brevity but I did test using 60 names. Is this what you wanted?

Children Tab
You didn't say how you wanted the results shown so I've assumed a new schedule each day (so I'm using the date to modify calculation results) and I've assumed if you have children with the same name then you employ some kind of unique identifier yourself. This takes the ASCII table position of the 1st, 3rd and last character of their names multiplied then divides by the day, month (multiplied by 37) and year. I then pick the 3rd, 4th and 5th digit of that generated number as the "pseudo random" number. NOTE: There is no special significance to this calculation, it was just made up at 'random'.

Timetable Tab
This shows the 4 classes and 15 seats for each in the 60 rows. The dates are copied from the Children tab into row 1 and for each cell where you want a name it indexes into the list of 60 names using the unique calculated rank of the "pseudo random" number.

The unique rank ensures no name is repeated each day.

Scraffat.xlsx
	A	B	C	D	E	F
1		Name	28-Aug-21	29-Aug-21	30-Aug-21	31-Aug-21
2		Christine	61	35	11	89
3		Christina	32	7	84	62
4		Christy	9	78	49	22
5		Flora	44	19	96	74
6		Kayleigh	28	96	67	39
7		Mila	95	68	43	20
8		Adrian	3	76	51	27
9		Lucas	52	23	96	71
Children

Cell Formulas
Range	Formula
D1:F1	D1	=C1+1
C2:F9	C2	=VALUE(MID((CODE($B2)*CODE(MID($B2&" ",3,1))*CODE(RIGHT($B2,1)))/(DAY(C$1)*37)+(MONTH(C$1)*37)+YEAR(C$1),3,3))

Scraffat.xlsx
	A	B	C	D	E	F
1	Class	Seat	28-Aug-21	29-Aug-21	30-Aug-21	31-Aug-21
2	1	1	Marley	Alden	Mila	Jayda
3	1	2	Chloe	Flora	Alina	Theodore
4	1	3	Mila	Charlotte	Odette	Jim
5	1	4	Isabella	Rowan	Yana	Sebastian
6	1	5	Madeline	Braelyn	Mika	Anna
7	1	6	Laurel	Lorelei	Terry	Chloe
8	1	7	Christine	Jayda	Sophia	Melody
9	1	8	Landon	Chloe	Braelyn	Margot
Timetable

Cell Formulas
Range	Formula
C1:F1	C1	=Children!C1
C2:F9	C2	=INDEX(Children!$B$2:$B$61,RANK(Children!C2,Children!C$2:C$61,1)+COUNTIF(Children!C$2:C2,Children!C2)-1)

 

[scraffat]

Hi Scraffat,

The obvious solution would be to use one of the RAND functions but the challenge is that if you make a change with causes it to recalculate then a new set of random numbers is generated and so your results change. You could generate random numbers then copy&paste as values to fix them but I wanted to avoid a manual task and you'd still have to handle duplicate results.

I'm just showing a part of each table for brevity but I did test using 60 names. Is this what you wanted?

Children Tab
You didn't say how you wanted the results shown so I've assumed a new schedule each day (so I'm using the date to modify calculation results) and I've assumed if you have children with the same name then you employ some kind of unique identifier yourself. This takes the ASCII table position of the 1st, 3rd and last character of their names multiplied then divides by the day, month (multiplied by 37) and year. I then pick the 3rd, 4th and 5th digit of that generated number as the "pseudo random" number. NOTE: There is no special significance to this calculation, it was just made up at 'random'.

Timetable Tab
This shows the 4 classes and 15 seats for each in the 60 rows. The dates are copied from the Children tab into row 1 and for each cell where you want a name it indexes into the list of 60 names using the unique calculated rank of the "pseudo random" number.

The unique rank ensures no name is repeated each day.

Scraffat.xlsx
	A	B	C	D	E	F
1		Name	28-Aug-21	29-Aug-21	30-Aug-21	31-Aug-21
2		Christine	61	35	11	89
3		Christina	32	7	84	62
4		Christy	9	78	49	22
5		Flora	44	19	96	74
6		Kayleigh	28	96	67	39
7		Mila	95	68	43	20
8		Adrian	3	76	51	27
9		Lucas	52	23	96	71
Children

Cell Formulas
Range	Formula
D1:F1	D1	=C1+1
C2:F9	C2	=VALUE(MID((CODE($B2)*CODE(MID($B2&" ",3,1))*CODE(RIGHT($B2,1)))/(DAY(C$1)*37)+(MONTH(C$1)*37)+YEAR(C$1),3,3))

Scraffat.xlsx
	A	B	C	D	E	F
1	Class	Seat	28-Aug-21	29-Aug-21	30-Aug-21	31-Aug-21
2	1	1	Marley	Alden	Mila	Jayda
3	1	2	Chloe	Flora	Alina	Theodore
4	1	3	Mila	Charlotte	Odette	Jim
5	1	4	Isabella	Rowan	Yana	Sebastian
6	1	5	Madeline	Braelyn	Mika	Anna
7	1	6	Laurel	Lorelei	Terry	Chloe
8	1	7	Christine	Jayda	Sophia	Melody
9	1	8	Landon	Chloe	Braelyn	Margot
Timetable

Cell Formulas
Range	Formula
C1:F1	C1	=Children!C1
C2:F9	C2	=INDEX(Children!$B$2:$B$61,RANK(Children!C2,Children!C$2:C$61,1)+COUNTIF(Children!C$2:C2,Children!C2)-1)

You're a wizard!!! THANK YOU!

 

[Toadstool]

You're a wizard!!! THANK YOU!

You're welcome!
Thanks for the feedback.