How to randomly assign names with maximum times (VBA or formula)

[Perez199202]

Hello Excel experts!

I have a list of people that needs to have cases assigned.

The formula can be added in the Assignee column (Cases sheet, Column C).

This needs to be randomly however with a maximum of repeat times based in another cell value.

Example:

Member Standard priority High Priority
Andres 7 2
Catalina 4 0

* High Priority it's only when the Priority Level (Cases Sheet) is 3,4 or 5.
* Standard priority is 1 or 2.

Also if the task owner (Cases Sheet) is the same as the Assignee has to run random again until gets a different name.

I haven't tried a formula as I can execute randbetween but I strictly need the that if has a maximum of assigments count.

[Kaper]

Hi,

Formula will not be easy here.

I first tried to use Solver, and it worked fine for such a limited set of data.
But probably you have much more than presented (especially in sheet2), so I prepared a VBA code to deal with it.

Please Login or Register  to view this content.

Note1:
Formulas in Sheet1 (column F nad right) and Sheet2 - column J
They can be removed and are there just to verify results.

Note2:
because of RndBetween used to assign staff to cases, people with highest capabilities (see Claudio in your sample) usually have less (proportional) load than those with lowest (as Catalina or Diego). Changing this would require change in random assignment procedure, which shall promote these with high numbers in columns C od D in Sheet1

[Perez199202]

That's was I was looking for!

Thank you very much Kaper!