• 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 to team members

Hi Chandoo,

I want a macro where i will be able to allocate work as per the described details below.

In the attend sheet there are the names of the associates which varies everyday as per attendance. And the number of task to be aasigned to them also varies evry day.

In the second tab named as assignment contains the policy numbers which needs to be assigned as per the mentioned number in the attend sheet to each members.

The policy numbers is my received volume everyday, which varies everyday.

The problem is i dont want to repeat those names in serial manner like:

Name No of task to be assigned
Sami 2
Ami 3
Rahul 2

Sami
Sami
Ami
Ami
Ami
Rahul
Rahul

I want the names to be repeated like
Sami
Ami
Rahul
Sami
Ami
Rahul
Ami


The count of policy number is always match with the sum of no of tasks assigned to members.

I have uploaded a file for your reference.

Please help me..i am in big trouble..i will appriciate from my heart if i will get the resolution asap
 

Attachments

  • work allocation.xlsx
    8.6 KB · Views: 98
Hi Narayan,

Appreciate your quick response .

However in the attached sheet i have increased the count of policy numbers, and add two more members name .

now its not working.

And each time its difficult to change the ranges in formulas as per available data.

Actually it will be helpful if you can create a macro which will do the allocation.

Note: the attendance , Count of policy numbers and no of task assigned to each person changes everyday.
 

Attachments

  • Work Allocation 2.xlsx
    14.2 KB · Views: 35
Hi ,

Attached is your workbook , with a formula-based solution.

The output formula is in column C , while columns D through P are helper columns.

If this is not acceptable , let me know.

Narayan

Hi Narayan,

Appreciate your quick response .

However in the attached sheet i have increased the count of policy numbers, and add two more members name .

now its not working.

And each time its difficult to change the ranges in formulas as per available data.

Actually it will be helpful if you can create a macro which will do the allocation.

Note: the attendance , Count of policy numbers and no of task assigned to each person changes everyday.



New
Hi Narayan,

Appreciate your quick response .

However in the attached sheet i have increased the count of policy numbers, and add two more members name .

now its not working.

And each time its difficult to change the ranges in formulas as per available data.

Actually it will be helpful if you can create a macro which will do the allocation.

Note: the attendance , Count of policy numbers and no of task assigned to each person changes everyday.
 

Attachments

  • Work Allocation 2.xlsx
    14.2 KB · Views: 42
Hi ,

What will be the maximum number of names possible ?

What will be the maximum number of policies possible ?

Narayan
 
Hi ,

See the attached file.

I have defined 3 named ranges ; if you wish to transfer the code to your working file , copy it into the worksheet section which relates to your data i.e. if your data is in a tab named Sheet5 , then copy the code into the worksheet section labelled Sheet5.

Create the 3 named ranges in your working file.

Run the macro named Sheet2.AllocateNames

Narayan
 

Attachments

  • Book55.xlsm
    16.6 KB · Views: 176
Hi ,

See the attached file.

I have defined 3 named ranges ; if you wish to transfer the code to your working file , copy it into the worksheet section which relates to your data i.e. if your data is in a tab named Sheet5 , then copy the code into the worksheet section labelled Sheet5.

Create the 3 named ranges in your working file.

Run the macro named Sheet2.AllocateNames

Narayan


Narayan thanks buddy....u r awesome...excel master....
 
upload_2017-10-30_22-35-25.png

Another formula base solution with helpers,

2 worksheet were combined together for easy checking

1] helper1I2, copied down :

=LOOKUP(ROW(A1),SUMIF(OFFSET(B$1,,,ROW($1:$10),),"<>")+1,A$2:A$9)&""

2] helper2 J2, copied down :

=IF(I2="","",COUNTIF(I$2:I2,I2))

3] helper3 K2, copied down :

=IF(I2="","",SMALL(J$2:J$39,ROWS($1:1)))

4] policy assigned names G2 , copied down :

=IF(F2="","",INDEX(I$2:I$39,AGGREGATE(15,6,ROW(I$2:I$39)-ROW(I$1)/($J$2:$J$39=K2),COUNTIF(K$2:K2,K2))))

Regards
Bosco
 

Attachments

  • allocation (1).xlsx
    15 KB · Views: 107
Hi ,

See the attached file.

I have defined 3 named ranges ; if you wish to transfer the code to your working file , copy it into the worksheet section which relates to your data i.e. if your data is in a tab named Sheet5 , then copy the code into the worksheet section labelled Sheet5.

Create the 3 named ranges in your working file.

Run the macro named Sheet2.AllocateNames

Narayan

Hi Narayan,

I am trying to understand your VBA code, But I got struck in the below statements.

ReDim AssignedArray(1 To [NumberOfNames])
..
currentname = [Employees].Cells(i).Value
currenttasks = [NumberofTasks].Cells(i).Value

I am wondering that you have not initialized any of these variables("NumberofNames","currentname","Currenttasks","employees" and "NumberofTasks") but its working fine. So could you please explain me how this is working?
 
Hi Narayan,

I am trying to understand your VBA code, But I got struck in the below statements.

ReDim AssignedArray(1 To [NumberOfNames])
..
currentname = [Employees].Cells(i).Value
currenttasks = [NumberofTasks].Cells(i).Value

I am wondering that you have not initialized any of these variables("NumberofNames","currentname","Currenttasks","employees" and "NumberofTasks") but its working fine. So could you please explain me how this is working?


Even I am trying to understand the same but can not reach there, someone please help.
 
Hi ,

In Excel VBA , any name enclosed in square brackets [ and ] is a named range , which has been created using the Name Manager.

Thus , [NumberOfNames] , [Employees] , [NumberofTasks] are all named ranges ; if you click on Name Manager in Excel , you can see the definitions of these named ranges. Since they are created within Excel , and not from within the VBA code , they do not have to be declared or initialized within the code.

They can directly be assigned values.

Narayan
 
Hi ,

In Excel VBA , any name enclosed in square brackets [ and ] is a named range , which has been created using the Name Manager.

Thus , [NumberOfNames] , [Employees] , [NumberofTasks] are all named ranges ; if you click on Name Manager in Excel , you can see the definitions of these named ranges. Since they are created within Excel , and not from within the VBA code , they do not have to be declared or initialized within the code.

They can directly be assigned values.

Narayan

Thank you soo much for quick response and helping out.

Thanks,
Aman
 
View attachment 46939

Another formula base solution with helpers,

2 worksheet were combined together for easy checking

1] helper1I2, copied down :

=LOOKUP(ROW(A1),SUMIF(OFFSET(B$1,,,ROW($1:$10),),"<>")+1,A$2:A$9)&""

2] helper2 J2, copied down :

=IF(I2="","",COUNTIF(I$2:I2,I2))

3] helper3 K2, copied down :

=IF(I2="","",SMALL(J$2:J$39,ROWS($1:1)))

4] policy assigned names G2 , copied down :

=IF(F2="","",INDEX(I$2:I$39,AGGREGATE(15,6,ROW(I$2:I$39)-ROW(I$1)/($J$2:$J$39=K2),COUNTIF(K$2:K2,K2))))

Regards
Bosco
Hello, thank you a lot for the sheet. It is really helping me for allocating task.
I am having an issue of adding names, when adding after line 11, it is not taking it in the allocating column G. Can you please help me?
 
Back
Top