Best way to use Solver to assign staff to roles based on their preference and suitability?

Hi there,

I've just joined and am happy to have found such a community online.

I'm hoping for some advice on a better way to use Solver to find an optimal solution for the below:

Situation: There are 400 roles open for application in the same bulk round. It's expected between 600 to 1500 staff will submit an application for one or more roles. Staff will be able to submit up to 4 applications and will have to nominate preferences for them (1 to 4).

Suitable applicants for each role will then be ranked (non-suitable applicants will not get a rank and cannot be appointed to the role).

Problem: How can I use Excel to auto assign staff to roles so that we optimize the staff Preferences and suitability Rank?

I've set up a Solver worksheet with some sample staff, roles and Preferences and Ranks for them (see attached file)

My current Solver approach has a big problem - the 200 variable cell limit prevents being able to analyse all the Roles and Staff at the same time.

Is there a better way to do this analysis so that I can get around this 200 cell limit?


A second request is for any tips on how to best set up the needed constraints dynamically rather than manually - given the dataset size?

Example Constraints Needed:

1. All staff ranked 1 for any role should always be assigned to a role (not left unassigned) as they will be dominant in at least the role they are ranked 1

2. Prevent a staff member being assigned to a role if another staff member has a higher rank for that role and has either not been assigned a role or has been given a lesser preference

3. Prevent staff from not being assigned if they have a higher rank for any role than an assigned staff member


As I'm sure this similar types of problems (preferences vs ranks) have been analysed many times, so if you have any templates, examples or reference links please share them.

Thanks very much,
Adrian

solverhelp01.jpg