Complex Constraint Satisfaction

[Elwood Blues]

Hi all,

This is my first post because this is the first time I have not been able to find a solution to my problem after searching these forums.

I don't know if Excel is able to solve a problem like this, but I thought I would ask a large community of super users and see what they say. Here goes:

I am in charge of placing a large number of children into skill groups. In the past I have just brute forced it and it sometimes takes me >6 hours to get it all done. I have figured out how to do the error checking and various formatting needs with formulae, but would really like a solution to put the kids into skill groups for me, keeping in mind certain constraints. I have looked at various timetabling software, but none seem to solve the constraints I have. Here they are:

-all children have to be in 3 skills
-there are 3 skill blocks
-no skills can duplicate
-children can only be in 1 skill at one time
-they select their top 5 choices and they need to be placed in as high an order as possible for the 3 skills
-there is limited amounts of space in each skill
-most skills run at each of the 3 skill block, but not every one
-I would like to try an avoid having 3 kids in each skill block for 1 activity by
condensing them all into one block

Any Excel ideas? Any other ideas?

Thanks for your time everyone!!

EB

[Leith Ross]

Hello EB,

Do you have a finished worksheet you can post as an example?

[Elwood Blues]

I'll work on posting a smaller example that will get the point across.

Thanks for the response.

EB

[shg]

Before you post your example, please take a few minutes to read the forum rules, and then amend your thread title accordingly.

Thanks.

[Elwood Blues]

Hi all,

Here is a link to a Google doc that shows a solved solution with altered names as well as the original skill choices.

https://spreadsheets.google.com/ccc?...5SGtmNEE&hl=en

As always, I had to make some compromises in the rules structure:
-I had to add 2 extra seats to BEADWORKING
-I had to add an extra seat to CANOEING & KAYAKING
-A few people I couldn't make work, so looking at their other skill choices put them in a skill that they might like. If I had more time I could make it work without putting someone in a skill they didn't request.

Thanks for your time!! I really appreciate it.

EB

PS- You may recognized this list from Top Gear's "Star in a Reasonably Priced Car," celebrities.

[Elwood Blues]

Hey Leith Ross,

Did the Google doc work for you?

EB