Random array of staff on duty with conditions based on weekday presence

Hello,

I am trying to create a random array based on a list of staff names to be on duty on a rotational basis. Duty days should be equally distributed throughout the year, but (here is the issue) the list should also consider the days of the week each staff is present at the office. For instance, if John works in presence on Tuesdays and Friday, he cannot be on duty on Mondays, etc...

I managed to create the list, using RANDARRAY, TOCOL, EXPAND, FILTER and other dynamic array functions, but I need help in figuring out how to consider the weekday presence factor. Any hint, really appreciated.

Test file here.

TestOfficeCoverageNov2023.xlsx

Thanks,

F.

Thank you so much, it seems to work perfectly. Very advanced stuff (to me at least). I will study it carefully as I am sure I will learn a lot from it.
F.