Minimizing Daily Course Schedule Conflicts

[tthiele]

I have a spreadsheet with a list of student names signed up for various courses, similar to what follows (although much, much larger, with more students and more courses):

Anna Underwater Basketweaving
Bob Underwater Basketweaving
Carl Underwater Basketweaving
Dan Underwater Basketweaving
Evan Underwater Basketweaving
Carl Yoga
Dan Yoga
Fred Yoga
Ginny Yoga
Harriet Yoga
Anna Intro to Econ
Dan Intro to Econ
Tom Intro to Econ

What I would like to be able to do is use Excel to assign a class period (from 1 to 7) to each of the courses (Underwater Basketweaving, Yoga, Intro to Econ) in a third column, and then count how many "conflicts" arise where a particular student has more than one class in a given period. The spreadsheet would ideally keep assigning class periods for all 7^n possible period combinations (where n is the number of unique courses in my list) and keep track of the optimal period arrangement, and then spit out the actual conflicts at the end.

Even if assistance provided on this forum can only help me do part of what I seek, that will be better than what I have to do currently which is "guess" at what periods will work best, assign them, and then manually count conflicts.

Thanks,

Troy

 

[Jerry Sullivan]

Hi Troy,

If you are assigning the periods manually, a COUNTIFS formula could be used to identify rows in which the combination of Name + Period occurs greater than 1 time.

To automate the assignment of periods using VBA, I would think you have some additional constraints to consider, such as maximum/minimums class sizes and whether all courses are available for all 7 periods.

With those constraints, an application that assigns classes with an optimization goal in mind is feasible- but not simple.