VBA routine to allocate (randomly) given 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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Persons[/TD]
[TD]16 persons – named A to P[/TD]
[/TR]
[TR]
[TD]Team[/TD]
[TD]Each person is a member of a Team of 4 – Alfa, Beta, Delta, Gamma[/TD]
[/TR]
[TR]
[TD]Periods[/TD]
[TD]There are 8 periods - 2015 to 2022[/TD]
[/TR]
[TR]
[TD]Courses[/TD]
[TD]There are 8 courses - 1 to 8[/TD]
[/TR]
[TR]
[TD]Area[/TD]
[TD]Courses are subgrouped in areas (Corses 1, 2 and 3 as Area 1 etc.)[/TD]
[/TR]
</tbody>[/TABLE]

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.jpg


My thought is

1. 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??)
2. 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)
3. 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

 

[MagicHansen]

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