VBA macro to allocate persons to courses over periods - with restrictions...

[MagicHansen]

I have a problem, and I think I best can solve it with a VBA code. So I really hope for some good ideas….....I try to explain

Basically i try to allocate persons to courses (groups) given a set of restrictions. The following just to give an example and explain the variables:

Persons 16 persons – named A to P
Team Each person is a member of a Team of 4 – Alfa, Beta, Delta, Gamma
Periods There are 8 periods - 2015 to 2022
Courses There are 8 courses - 1 to 8
Area Courses are subgrouped in areas (Corses 1, 2 and 3 as Area 1 etc.)

In reality the number of Persons and Teams may vary a lot. Periods and Courses will be more or less constant.

Restrictions (or conditions):

each person attend one course per year
each person must attend all 8 courses over the 8 periods
each course is followed by 2 persons, ie 8 courses with each 2 persons per period
persons from same team should not attend same course (or even better a counter/rule counts the number of case – and I can set a rule to iterate a new solution if the number is too high)
persons across teams should be mixed as much as possible (or like above – a counter/rule and iterate if there are too many)
a person should not attend courses in the same Area in successive years (the is nice to have, not need to)

It could be illustrated like this – each person gets a number 1 to 8 (in the rows), and each period (column) contains number 1 to 8 (2 of each)

https://dl.dropboxusercontent.com/u/12042084/Class.png


My thought is

first to allocate (more or less) randomly to the courses – but somehow aided so it doesn’t have to do loops over and over until the numbers fit (does this make sense??)
then define rules to check for the mixing of persons - and perhaps also teammembers in same course (if this can’t be handled in step 1)
depending on the rules in 2) it could accept the solution or reiterate (back to step 1)


Im so so VBA skilled, and the only way I can resolve step 1 is endless looping (I think), but im afraid that would affect handling time??

So any suggestions, please??

Best regards
Michael

[mrice]

One way to do this is to create a scoring function which gives you a number where a positive weight is given to the things that you want and a negative to those that don't. Make an initial random assignment and calculate the function. Then swap two people at random and recalculate. If the score improves then keep the change and swap another two at random and repeat. If the score is worse then revert before trying another swap.

It's worth keeping a count of the number of swaps since the last improvement so that you can set a threshold at which to stop.