Selected Answer
Finally, I got all the kinks I could think of ironed out. For further testing please find the resulting file attached. Operation is easy. Just double-click on any of the weekly captions, Wk 01 and up in the attached workbook. The assignments will be entered in the column below the clicks.
Note that the system looks for previous assignments to the left of the column currently being filled. If you fill columns out of turn there will be logical irregularities.
You can rename the captions in any way you like. The program doesn't look at the captions, just the location you clicked. It's the same with the students names and with the job names. Any number of students is OK, and any number of jobs (but not more jobs than students!). You can rename the worksheets, too. There are more instructions on the JobsList tab.
The logic I implemented is to assign jobs in random order. Any student can get any job or non at all. Preference for job assignments is given to students who had fewer jobs in the past; random selection among equals. Finally, when assigning individual jobs, preference is given to students who didn't have that job before, failing which, a student who had the same job the longest time ago. Not much remains truly random after all these rules are applied but enough to make it difficult to predict the next list of assignees.
This is a macro enabled workbook. Your security settings must permit the running of macros. The code supports most possible layout changes you might want to make to the worksheets. You will find some instructions and explanations in the code. Look for the code in the code sheet pertaining to the Assignments worksheet. All the code is in that module. There are no parts of the code anywhere else.