Optimising allocations based on preferences

[jcampbell]

Hi, I have to allocate students to a sport. Each sport has a limited number of places. Students select preferences from 1st to 4th. I am trying to optimise the allocation so that all students get the highest preference allocation possible. I looked into whether I could use solver but I don't think it is possible (I have up to 600 students across about 35 sports). I also tried a code from another thread "Re: Help creating model to allocate preferred choices but with limited no. of allocations per choice to maximize ranked preferences", however the allocation seems to depend on the processing order. I think I may need to allocate all to their first preference first, regardless of limit on spaces, then if sport is overallocated look at the second preference for that sporting group. This may mean that someone else gets bumped from their 1st preference assuming 2 people with their 2nd preference is better than one with their 1st and another with their 3rd or 4th. Can anyone see a way this can be solved using excel? Many thanks.

 

[myall_blues]

I think this can be solved using VBA.
I assume the fair way to do this would be to assign each student a random number from 1 to 600, after which the logic goes something like:

• For student 1, check if their first preference is full
• If no, assign that preference
• If yes, try and give next available preference.
• Move to next student
• Continue through all students

If you can provide some test data (like the data shown in your image), preferably using XL2BB, I’ll give it a go.