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
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:
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:
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.
Bookmarks