Excel 2007 : Want to use Excel to generate a training schedule without conflicts

[ryanmosel]

I work as a consultant on an SAP project where we need to develop a training schedule for end users. The training schedule is based on the roles assigned to end users. End users may have more than one (in many cases they do) role assigned to them. The training duration varies by role/class. I currently have a list of ~300 users who need training across 40 classes. The classes may only be offered 1 time or they may be offered multiple times. I've tried to come up with a way to schedule the end users to classes so that they aren't supposed to be in two different classes at the same time but, I've come up fruitless in my efforts. I've attached a file which has all of the data that should be relevant.

CourseReqTable tab = By user which course they need with an "x" in the course column

Availability tab = This was a table I created with the weekdays that classes occur. What I originally was going to do was write some sort of formula that checked the CourseReqTable sheet by person to see which classes were required and mark an "x" in the availability calendar each time they are supposed to be in a class but, that seemed too complex for my excel skills as of right now.

Course Schedule tab = Contains the dates and durations of the courses by name

EUT Calendar tab= The visual calendar of when the classes are currently scheduled.

Scheduleing Nightmare.xlsx

My question(s) for the forum is: What is the easiest way to schedule the classes while minimizing the creation of new occurences of a class? Would this be something that could be accomplished in Access? I'm open to suggestions and even open to moving the schedule around completely to accomodate the least amount of scheduling conflicts and new occurences. Please help!

[shg]

Every college faces this issue -- there is surely commercial software to do it.

It would be a non-trivial piece of code to write.