Filling empty cells with random values from a list

[Jianychta]

Hello everyone.

I'm trying to fill in the empty cells randomly with a list of values, but these values cannot be duplicated on the same row and column, and can only be duplicated a set number of times.

I'll attach the sheet here.
I want to fill in the empty cells of the "Time/Teacher" table with the names from the "Student Name" list, but the student name cannot be duplicated within the same column and row, and can only be duplicated 5 times (# of classes)

Any advice would be greatly appreciated.

[menem]

I think my formula is not so good, other should be done better.
(It's not random just sequence)

Regards.

Please try.

F2 copy to J5
=INDEX($A$2:$A$9,
IF(MOD(((ROWS($F$1:$F1)-1)*5)+COLUMNS($F$1:F$1),COUNTA($A$2:$A$9))=0,COUNTA($A$2:$A$9),
MOD(((ROWS($F$1:$F1)-1)*5)+COLUMNS($F$1:F$1),COUNTA($A$2:$A$9)))
)

F7 copy to J10
=INDEX($A$2:$A$9,
IF(MOD(((ROWS($F$1:$F5)-1)*5)+COLUMNS($F$1:F$1),COUNTA($A$2:$A$9))=0,COUNTA($A$2:$A$9),
MOD(((ROWS($F$1:$F5)-1)*5)+COLUMNS($F$1:F$1),COUNTA($A$2:$A$9)))
)

[Jianychta]

Thank you for your help. I will keep this as a reference for future excel tasks.

[HansDouwe]

Please empty the cells with LUNCH and try in F2 for the whole table inclusive LUNCH:

=LET(r,SORTBY(SEQUENCE(8),RANDARRAY(8)),s,SORTBY(SEQUENCE(8),RANDARRAY(8)),SORTBY(EXPAND(MAKEARRAY(8,5,LAMBDA(x,y,INDEX(A2:A9,1+MOD(INDEX(s,x)+INDEX(r,y),8)))),9,5,"LUNCH"),{1;2;3;4;6;7;8;9;5}))

Press F9 for a new random result.

[Jianychta]

That's awesome! It's amazing seeing everything get randomized every time I press F9. Thanks a lot for helping!
Hope you guys have a nice day, always.

[Jianychta]

I apologize, I had one last question. If I were to add in more students and Teachers, which parts of your code should I alter?

[HansDouwe]

When there are more students, are there also (just as many) more hours?
If that is not the case, additional rules are required: Should several students be assigned to 1 teacher at some hours?
Or are there students who do not come to certain teachers.