• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro required for assigning tasks to employees based on workflow rules

Patch500

New Member
Hello!

I'm hoping someone can kindly help me create a spreadsheet that can allocate any number of claims (total claims varies each day) to employees based on the following rules.

Rules

There are 5 (A,B,C,D,E) employees. Each employees total claims should sit around the following percentages; A =15.5%, B = 15.5%, C = 23%, D = 23%, E = 23%.

The percentage of claims each employee currently has will vary based on previous allocations. Therefore new allocations will need to be based on the current allocation percentage.

Claims are in numerous currencies: USD, AUD, NZD, THB, MYR, VDG, SGD. Only employees B and C should receive claims that are of THB, MYR, VDG and SGD currencies.

If employee A's percentage requires a claim/s to be allocated to them it needs to be the biggest value.

If a claim is greater than 250,000 in either USD, AUD or NZD it needs to be allocated to employee A.


I would appreciate any help.

Thanks in advance! :)
Patch
 
Hi ,

Please upload a sample workbook , so that realistic data is available for testing any code that is developed.

Narayan
 
Hi Narayan

I have attached a sample of the excel spreadsheet.

Thank you!
P
 

Attachments

  • Sample.xlsx
    74.9 KB · Views: 5
Hi ,

Thank you for uploading your file ; however a few questions still remain :

1. Where is the input data ?

2. Where should the macro put in the output ?

As far as I can understand your explanation , the rules are as follows :

a. Only employees B and C should receive claims that are of THB, MYR, VDG and SGD currencies.

b. If employee A's percentage requires a claim/s to be allocated to them it needs to be the biggest value.

c. If a claim is greater than 250,000 in either USD, AUD or NZD it needs to be allocated to employee A.

Given the above rules , does it mean that employees D and E are not governed by any rules ?

Rule b may require some more explanation.

Narayan
 
Hi again :)

I've changed the sample spreadsheet a bit, so hopefully I can be clearer in what I'm after.

Essentially I would like a macro to allocate the new claims between 5 employees. The 'workflow' tab counts all the claims that have been allocated to each assessor on an aggregate basis (Cells D2:H2). This percentage needs to be factored in when allocating the new claims, the other factor is the required percentages in the 'overview' tab - cells J4:J8.

Instructions

The claims that needs to be allocated are input into cells b4:e25. Not all of these cells will be used each day, essentially it can be 1-22 tasks that need allocation.

(a) Correct, but I changed the labels sorry. Only the employees labelled EXS and NEG should be allocated claims that are of THB, MYR, VDG or SGD currencies.

(b) Correct, but I changed the label to KCC. I'm hoping I clarified this a bit more in the second paragraph.

(c) Correct, but now labelled KCC

(d) The other two employees (PTB and PEC) do not get currencies THB, MYR, VDG or SGD claims - that is the only rule.

Based on the claims entered in b4 to b7 on the overview tab, I would allocate the claims as follows:
1. PEC, 2. EXS, 3. PEC, 4. KCC

In addition how difficult would it be to transfer this information to the 'workflow' tab? I have entered the data into cells I19 to L19. Background colour needs to be pink and font colour white.

I hope this isn't too difficult? It's all way above my head!

Thanking you!
P
 

Attachments

  • Sample Update.xlsx
    75.6 KB · Views: 6
Hi ,

If it is way above your head , imagine where it must be for the rest of us !

It will take some time to go through all that you have explained ; I think you should check back later.

Narayan
 
Hi ,

Just finished going through your file and explanation.

My first comment is that this may not be a one-off exercise , but is something which you will be using again and again. Please confirm.

If the data entry range is restricted to B4 : F25 , what will happen once this range is filled with data ? Will you enter fresh data in row 26 onwards , or will you clear this range , and start fresh data entry from B4 again ? If it is the former , a formula-based solution can be thought of , but if it is the latter , then a formula-based solution is not possible , and VBA will be needed. Please clarify.

What is the range L2 : P8 , as well as the list of holidays being used for ? I can see that the formula in column B on the Workflow tab uses them , but what is the significance of this calculation ? Is it for finding out the earliest date on which fresh claims can be allocated ? Please explain.

My understanding of the correlation between the earlier labels of A , B , C , D and E , and the current labels is as follows :

A - KCC ; B - EXS ; C - NEG ; D - PTB ; E - PEC

Please confirm.

If I try to understand how you have allocated the 4 claims to the assessors , it would be as follows :

Priority 1 : If a claim is greater than 250,000 in either USD, AUD or NZD it needs to be allocated to employee A.

Thus , given that the amount 1,000,000 is greater than 250,000 , and given that the currency is AUD , claim #4 will be allocated to assessor KCC.

Priority 1 : Only the employees labelled EXS and NEG should be allocated claims that are of THB, MYR, VDG or SGD currencies.

Given that claim #2's currency is MYR , it can only be allocated to EXS or NEG. Between the two of them , EXS has a current allocation of 20.63 % , while NEG's current allocation is 31.75 % ; thus between the two of them , priority should be given to EXS.

I have named both of the above Priority 1 , since they are mutually exclusive ; the first rule applies only to currencies USD , AUD or NZD , while the second applies to all other currencies.

Priority 2 : Claims #1 and #3 are not governed by the above 2 rules , and hence can be allocated in any manner , preference being given to those assessors whose current allocation is less than their limit. Thus , PEC with a current allocation of 11.11 % against a limit of 22 % gets the two claims.

Is all of the above correct ? Can these be made the logic for allocating all claims ?

In case you can add further explanations , please do so.

Narayan
 
Back
Top