Sorting subjects into groups

[tony_winter]

Hi,
I wonder if anyone can get me started with this!

I’m involved in the construction of the timetable at my school. I have taken a list of subject choices for students in a particular year group and used Excel to convert this information into a table. Along the top row is a list of subjects. Down the first column is the same list of subjects in the same order. The value of each cell in the table is a number representing the number of pupil doing both the subject in the column and the subject in the row i.e. the number of students involved in a clash between the 2 subjects ( FOR INFO: the values on the diagonal of the table represent the number of students opting for each particular subject).

MY PROBLEM:
As a timetabler, I’d like to sort the subjects into 4 roughly equal groups (lets say there are 30 subjects in total so that would be 7ish per group), making sure that the clashes between the subjects are minimised (IDEALLY I’d like there to be zero clashes between subjects).

Some subjects can appear in more than one group e.g. since there are 3 History sets, I can have History appear in 3 groups; since there are 2 Physics sets, I can have Physics appear in 2 groups. The number of groups is determined by me in advance.

I can do this manually but would rather let excel rattle through a number of possible permutations (maybe start with a randomly chosen subject, and let excel make a number of passes through the algorithm). I’m familiar with parts of the Excel object model and have done some VB/VBA programming before.

Thanks for getting this far; I realise this is a bit of a long winded problem, but please help!

Tony