Workflow help Required

Question

Dear Team,

I have 1015 cases to be randomly allocated to QC executive.

This allocation depends upon zone and manpower count.

I have attached sample data and QC logic.

In all there all 74 man power under 10 QC executive Spoc. Hence 1015 / 74 means around 14 cases per head.

Let's take example of Sampath Kannuri . He has man power 7 so cases to be allocated to him will be max 96. But these cases should not be from his own zone that is AP # TELANGANA # TN. He should get data from other than AP # TELANGANA # TN zone and so on.

These is our daily activity and it's not feasible to get it done manually as count varies.

Appreciate your help in advance.

QC Data Allocation Logic.xlsx

QC Sample Data.xlsx

abe_ibanez · Answer

Hello,

I would use an iterative macro to first filter out the cases in their zone, then randomly assign them x number of cases depending on their number of workers.

In order for it to be random, but still deterministic, I used the MD5 encoding of the case data. This way the process would be repeatable and auditable. If you want to change the order, you can just add a character to the MD5 encoding within the Macro.

Hope this helps,

New Workflow4.yxmc

New Workflow3.yxzp

OTrieger · Answer

@vishwa1979 
I remember a very similar if not exactly the same post, is it a weekly challenge?

You should try to find it.