Need Help Assigning Jobs to Students

In my department we have a laptop setup with Excel 2007 so that a student can walk in, scan his ID with a barcode scanner, and then be counted for attendance. This was very simple with the VLOOKUP formula. However, I have now been assigned the task of setting up a similar program only now I must make it so when the student scans his/her ID then it will give that student a random job.

I have three major problems when it comes to this:
1. I can't have jobs assigned to a student who is not there
2. I prefer the jobs be random, but cannot be given consecutively and must be evenly distributed

There are 25 students total that need to be involved, 13 juniors and 12 seniors with 8 jobs that need to be assigned. I will attach my template although I am able to be flexible on the setup.

There is no difference between the junior and senior class. The junior class will come in the morning, scan their IDs, and then receive a job. The senior class will then come in two periods later, scan their IDs, and receive their job. With there being more students there obviously has to be more or repeated jobs. Jobs 1, 2, and 3 will be given to two students during that period while there will be one or two assistant jobs as well. I now have it set up so that Column C will use the VLOOKUP function to look up the name associated with the ID. Then I have another table setup with their name matching a job with a macro that will randomize that list of jobs each day so everytime the student scans their ID, a new job will be associated with the student's name. Although my issues arise when jobs are randomly consecutive and with the code I have, it will randomize the whole column without separating juniors from seniors. I will upload the template so I can get a few opinions on what to do.

Basically I need help with making sure the jobs are not assigned to the same person within a week period as well as changing the existing macro to only randomize B1:B12: and B14:B26.

I appreciate any help or effort, thank you.

Ignore the last template. All I need to fix now is the consecutive job assigning.

A template is attached.

Thank you for all of the help.

Thank you for all your help.

I am open to any ideas for this project but unfortunately I do not have enough experience to go about accomplishing your proposition.

If you find any free time where you could make an example or something that I can implement into my project, it would be much appreciated. If not, no worries. I am at the point where what I have may have to be acceptable since other projects require my attention.

All help is much appreciated.

Thank you very much!

I love the look of that and the functionality, but unfortunately I am unable to use a template like that.
I'm stuck with the template I was given and I am very close to having every function work as I want it to, but I do really appreciate the effort given into this assignment.

I have finally assembled exactly what I need for this project but I have a few issues getting it to work correctly.

Here's what is exactly happening:
1. I type a student ID in A2. It will return the Date and the Name but I get the error message "Could not set array: 'Tasks.' This action has been abandoned," for the Job.
2. I type student IDs in A3:A6. Everything returns perfectly.
3. I type a student ID in A7. It will return the Date and the Name but I get the error message "Job assignment failed. This action has been abandoned," for the Job.
4. I type a student ID in A8. Everything returns.
5. Every ID I enter after A8 returns "Job assignment failed. This action has been abandoned."
ALSO, if I enter the ID in A3 that I enter in A2, Excel will go into a loop that I must force close.

I really would like this program to work so all help is much appreciated. A template is attached.

Hello Pierre,

I thank you very much for taking your time to try to understand this code and fix it. Unfortunately I did not see your post until fixes have already been made, but if you want to take another look so I can have another brain try to locate the next problem, I would much appreciate it.

My problem now is when I reach row 15. All cells work perfectly until I reach this row where it won't return the job and I am unsure why. (It occurs with every row after row 15 as well.) I will attach a template and if you have any solutions, let me know or just implement them into the project and attach it. Thank you.

I apologize for not reading closer. Thank you for all of your help.