Allocating a value based on a preset list whilst keeping allocation equal

Hi Guys,

Had to put my hand up on this one....

Basically we have 8 data centers and based on user department, everyone should have one datacenter allocated to them and have equal distribution. i.e. 1000 users means each data center will have 125 users spread evenly amongst departments.

First lot of allocation is cake though the challenge is maintaining equal allocation (by department) when staff leave and join. If Col. C has a value, this means this user has been allocated Datacenter x (and cannot change). The empty values represent new hires.

I've uploaded a sample sheet based on 100 staff and ~9 depts. to look at and hopefully this will give you a better picture of the requirement. Any assistance on getting a result would be appreciated.

thanks!