Removing random variables appearing twice

[surgiosphynx]

Hey guys!

I have a strange and hopefully not to tedious problem regarding how to remove a random variable appearing twice.

My task is to make a schedule for 7 people, where 2 will work on weekdays (One as a team leader and the other a reserve: On weekends there will be 2 team leaders & 1 reserve.

The problem I am having is that the same person is randomly selected as Team leader and as the Reserve.
Is there a code to make this person not appear twice on the same column?

This is the formula I've used so far for my 31 days is:
=INDEX(A#:A#,RANDBETWEEN(1,COUNTA(A#:#A)),1) <----------------- This randomly selects the 7 workers names from the List in Row A.
I used this same "Randomizer" twice. Once in the row for Team Leader and once for Reserve.


After being able to remove the same person appearing twice, I face another problem where I have to remove certain people from popping up on certain dates where they cannot work. This means I need to integrate the formula above with all the dates in the month and make certain people not get randomly selected for those specific dates which they book off from work.

In totality all 7 workers work 10 times per month (Total of 70 shifts in 31 days). 5 as reserve and 5 as team leader.
Excel seems to randomly give one person 17 shifts and the other 3.. Is there a way to evenly and randomly distribute the shifts out amongst the workers so they get 5 each and exclude the dates they cannot work.

Any pointers to the right direction will be highly appreciated

I would love to attach what I've achieved so far but cannot because of the forum rules but will be happy to mail anyone pictures or the excel file if they want to see what I've done so far.

 

[surgiosphynx]

I wrote incorrectly in the last paragraph.. The shifts are unevenly distributed between the workers.. Meaning they get 7 shifts as team leader and 3 as reserve. This should be 5 and 5. They all get 10 shifts each...


Sorry!