• 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.

Work Allocation based on skills and skill category

T

TakkarA

New Member
Hello everyone,

I am new to chandoo and posting here for the first time.

I need some help with automating the allocation of work based on skills and skills categories equally.

There are 3 skill levels skill 1, skill 2, skill 3 and there are 5 different categories A, C, L, M,T.

All the team members are not skilled in each category, so i have a skills matrix.

I need to allocate the applications coming in equally based on required skill and category to each team member having that skill.

Sample -
upload_2018-2-14_14-43-56.png

Skills Matrix - Name = Name of agent

Categories - T C L A M
Skills - 1 2 3

upload_2018-2-14_14-44-35.png

Any help will be appreciated.
Thank you
 
Last edited:
Can you also share which version of Excel you are using. From the look of things there could be a rather easy way to accomplish this with Power Query. But it needs to be available in you Excel version.
 
Hi ,

Your data has 5 out of the first 6 applications requiring the same skill level of 1 and the same bucket skill of T.

How will these 5 applications be allocated to the team members ?

Narayan
 
Hi Narayan,

If there is an application whiich doesnt have the person with same skill set/category then it can be given to the person with next skill that is skill level 2 and so on

but a skill level 3 application cannot be given to someone with skill 2 or skill 1
 
Also the maximum number of application we get are going to require skill level 3 - if we can clear the application with skill level 3 before the others that will make it easier i think.
 
TakkarA said:
Probably something on the lines of just one person to one application, and once the whole list of team members is exhausted then start from the top of the list again. so that the work is equally divided.
Click to expand...

Maybe,

D2, copied down :

=IFERROR(INDEX(Matrix3[Name],AGGREGATE(15,6,ROW(Matrix3[Name])-ROW(Matrix3[[#Headers],[Name]])/(INDEX(Matrix3[[T]:[M]],,MATCH(C2,Matrix3[[#Headers],[T]:[M]],0))=B2),MOD(COUNTIFS(B$2:B2,B2,C$2:C2,C2)-1,COUNTIF(INDEX(Matrix3[[T]:[M]],,MATCH(C2,Matrix3[[#Headers],[T]:[M]],0)),B2))+1)),"")

Regards
Bosco
 

Attachments

Last edited:
TakkarA said:
Probably something on the lines of just one person to one application, and once the whole list of team members is exhausted then start from the top of the list again. so that the work is equally divided.
Click to expand...
Sorry, TakkarA, I misunderstood completely the part of allocation before... Should have read it more carefully.
 
Thank you Bosco! I will go through it and come back to you.


bosco_yip said:
Maybe,

D2, copied down :

=IFERROR(INDEX(Matrix3[Name],AGGREGATE(15,6,ROW(Matrix3[Name])-ROW(Matrix3[[#Headers],[Name]])/(INDEX(Matrix3[[T]:[M]],,MATCH(C2,Matrix3[[#Headers],[T]:[M]],0))=B2),MOD(COUNTIFS(B$2:B2,B2,C$2:C2,C2)-1,COUNTIF(INDEX(Matrix3[[T]:[M]],,MATCH(C2,Matrix3[[#Headers],[T]:[M]],0)),B2))+1)),"")

Regards
Bosco
Click to expand...
 
You must log in or register to reply here.
Back
Top