Auto-populating roles based on list of authorisations

Hi and welcome
could you please add expected results manually? Thanks

One of possible approaches could be by using Excel Solver. It's a standard tool included with Excel, but not active. If you are not familiar with it you may start with Office help: https://support.office.com/en-us/art...c-e24772f078ca

The Model I prepared uses task numbers (names of the tasks can be just names) which are assigned to available staff.
The numbers are in G3:G5 (gray area). These are variables for the model. You can write there any initial set of numbers - for instance assign all to task 1 :-)

Then the formulas in column H (yellow cell and copied down) calculate how many occurences of given task is assigned (if you wrote all staff task 1 of course there will be 3 in all cells). its simple countif

Formula:

Please Login or Register  to view this content.

We will use this information as a required condition in solver - all countifs shall return 1 so no post has double assignment.

In column I we check if given person is authorized to work on this pre-assigned post. The formula is here a bit more complicated (because B2:D8 contains texts. Would be easier if there were only 1 for aut and 0 for non-auth). but basically it's Index/Match formula:

Formula:

Please Login or Register  to view this content.

Then we sum it all (green I6) and this cell will be the aim for our model we want here number 3 that shows number autrorised persons on their posts.

The solver will try to change cells in G3:G5 this way to end up with all cells in H being 1 and sum in I6 reaching 3

If you open the solver you will notice that there are also 3 other limits set: every value in range G3:G5 has to be integer (no fractions allowed) has to be larger or equal 1 and less or equal 3

Now after pushing Solve button in solver after few seconds you will get the solution.

To make life easer assigned tasks are shown not by theit numbers but with names assigned to them in row 8 with formula in F8 (again index/match type) and copuied right is:

Formula:

Please Login or Register  to view this content.

Install Solver and try it. Try to select Jack, Frank and Scott and notice Solver will not find a solution (as none of them is authorized for task 1).

The solution is in Sheet2 of attached file.
In sheet1 you will find solution usin binary values, but as I assume you have more posts than just 3 and more staff - the one proposed in Sheet2 would be better scaleable to larger tasks.

Indeed, Alldiferent makes sense, and would allow not using column H formula (and limits), so probably a bit quicker computations.
But I think it's anyway needed to give upper and lower limits to changed cells. So we still need 4 rows of constraints

Formula:

Please Login or Register  to view this content.

the file could be modified to use less natural (in non-programmers sense) numbering of tasks 0-2 but anyway upper limit (of 2) would be needed, and selected a "hidden constraint" of reqired all non-negative values checkbox .

Best,

If only the last task requires more than 1 person, the easy thing would be to use 1-12 as variables (column K with repetitions was not needed if we stick to AllDifferent constraint - so I deleted it)
I added a row with number of staff required
But as I said - with the model from the attached file only last task could absorb more than one person. So if you need say 2 persons for task 3, an easy workaround would be to make extra column for task 3b - could have exactly the same heading - we dont use it
I added also formula for counting of staff available today and this is already used in a model. So no need to manualy change model parameters!

OK, have to go off-line, but hope the formulas are rather easy to understand. SPARE staff member(s) obtain tasks with a number greater than total number of staff needed and are listed in extra column.

The model has now the aim set to maximum (not equal to some value) this way it laso dont have to change after changing available staff list. The staff avaliable shall be inserted in or deleted from the middle part of the table in columns I:K - this way we will avoid problems with ranges in formulas.

The same with inserting new staff in main table (A:G) - also insert rows in the middle, writhe autrorizations and (if needed) sort the table.

Play with the file :-)

Indeed, tried and it works.
Alldifferent alone.
My problem was that I used both alldifferent and integer. And integer required upper and lower bounds.

So thanks for the discussion and "drilling down" the subject. Again, learned somenthing new at forum.