Work assignment Macro

[Niranjanrajrishi]

Hello,

I need a Macro to reduce my work to allocate work items to my team. I have a team of 12 members and need to assign each category of work based on the volume. Let's say I have to assign work items A, B, C, D to 12 members. I have table wherein I will enter the count of work from each bucket and I want to assign the volume based on my table (manually prepared). Can anyone support me to build a macro with such scenario?

 

[Hui]

Can you please post a sample file with some sample data?

 

[Niranjanrajrishi]

Please find the attached sample file. Please check the sheets and the responsibility should considered as "Responsibility Basket & Doc Type" and then the count of work items should be considered from the Volume input sheet.

 

[Niranjanrajrishi]

Did you manage to work out for me?

 

[NARAYANK991]

Hi ,

Nothing is clear , to me at least.

1. Which cells should have formulae in them.

2. What should be the outputs in those cells ? Manually enter these values.

The more cells' outputs you provide us with , the easier it will be to understand your requirement.

Narayan

 

[Niranjanrajrishi]

Sorry if it was not clear.

My intention is to assign the work ownership in the column W. The names should consider from the sheet 'Volume_Input'. If you look at the sheet 'Volume_Input', you will see the Usernames in the column 'B'. These Users should get assigned the work. But the sheet 'User_Responsible' should be considered when assign the work. The work type will be available in column 'H' in the Data. So based on the target (manually) the ownership should be assigned in the volume_input should be assigned.

Please let me know if it works.

 

[NARAYANK991]

Hi ,

Sorry , but things are still not clear , since no manually worked out examples have been provided.

The user names in column B in the tab Volume_Input are identical to the user names in column B in the tab User_Responsible.

Unless you manually enter the user allocation for the responsibility basket AT01 , I doubt that we will progress.

Narayan

 

[Niranjanrajrishi]

Thank you so much for the direction. Please find the attached file wherein I have filled out the User responsible column manually.

Te User_Responsible sheet is to identify the responsible basket based on which we need to allocate the work. Not all Users will work with all basket. So based on the volume inserted in the 1st sheet we need to allocate applicable basket to each one. If still not clear, I will send the exhaustive list which we worked manually. Since the file is huge, I considered only few lines here.

 

[NARAYANK991]

Hi ,

Sorry , but it is still not clear.

What use is being made of the values in the Approvals , Overdue and SLA columns in the Volume_Input tab ?

You have indicated User_1 in some of the cells in column W in the Data tab ; why have the cells W38 through W41 , W64 , W65 , W68 through W72 , W95 , W96 ,and many more cells in the range W98:W166 in the Data tab not been populated with the user name User_1 ?

Narayan

 

[Niranjanrajrishi]

Approvals , Overdue and SLA - these are the status of the work basket. The utmost importance will be given to Overdue, then followed by SLA and Approval and at last scan today. I have not populated all values. I just given sample values.

If you need to entire file worked upon, then I will send it tomorrow after we work the file manually.

I appreciate you efforts.

 

[NARAYANK991]

Hi ,

That will be nice.

However , doing it for just User_1 alone will be more than enough.

Narayan

 

[Niranjanrajrishi]

Please see the attached file with manual allocation. I decided the no of works items after the total queue count. I want to get it allocated based on the work items I given in a table and the same assign to them.

 

[NARAYANK991]

Hi ,

The confusion still remains.

If we take the responsibility code AT01 , we find that some of the entries have the name Vipin , which I assume is Vipin Nirapath ; other entries have the name kshi ; I have no idea who this is.

Why have some entries been allocated to kshi , and why are there a lot of entries which have not been allocated to anyone ?

Unless the logic is clarified , I am afraid we will be wasting time in this back and forth.

Narayan

 

[Niranjanrajrishi]

Sorry! Something messed up. Please find the attached file with complete names.

 

[NARAYANK991]

Hi ,

Can we focus only on the responsibility code of AT01 ?

There are in all 92 such records.

Of these , you have allocated 17 to User_14 , 7 to User_12 , while 68 have been given the value Not Allocated.

How and why ?

Narayan

 

[Niranjanrajrishi]

Yes.

We given only17 to User_14 , 7 to User_12 , while 68 have been given the value Not Allocated since we cannot complete everyone on the same day. So what ever possible work will be given. We need to consider the responsibly for each User. As I mentioned, a table should be created and we manually insert the no of work items will be given to each User. The work items will be based on the work load. Let's say AT01 has 100 work items and we have I let one User available. So we will give , let's say 40 and other work items to make it 100 daily target. Once we do use the work items count, then the macrk should assign the owner ship.

 

[NARAYANK991]

Hi ,

I am tired of this. This is my last message in this thread.

1. Why did you allocate 7 to User_12 ? From where did this allocation come ?

2. You say :

a table should be created and we manually insert the no of work items will be given to each User.

Do this and upload the file.

3. The macro can be written only after everything else is in place ; upload the workbook once all the other elements , data , tables , are in place , so that what ever the macro needs to refer to is available in the workbook.

4. The logic of how the allocation should be done to one or more users , based on the various tables , is still not specified clearly ; explain this in detail with reference to the uploaded workbook.

Narayan

 

[Niranjanrajrishi]

Sorry if I make you more work with my requirement. I have made all the required details in the new file and let's close this if it does not help you to create the required coding.

Please refer the sheet "Input" to assign the work ownership in the Sheet Data (column - AA). Please use the column Z (Sheet Data) to find the User names from the sheet User_Responsible (Columns L:M).

Based on the work items assigned in the table (columns J:N) in the input sheet, please assign the ownership in the Data sheet. If you have any better report format for me, please include in the macro to communicate to the team with their work allocation.