work allocation to team members

[soumya ranjan choudhury]

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

 

[NARAYANK991]

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

 

[soumya ranjan choudhury]

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.

 

[soumya ranjan choudhury]

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

 

[NARAYANK991]

Hi ,

What will be the maximum number of names possible ?

What will be the maximum number of policies possible ?

Narayan

 

[soumya ranjan choudhury]

Hi ,

What will be the maximum number of names possible ?

What will be the maximum number of policies possible ?

Narayan

Maximum Number of names will be 60 and maximum number of policies will be 5000.

 

[NARAYANK991]

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

 

[soumya ranjan choudhury]

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

 

[bosco_yip]

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

 

[Rathank]

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?

 

[prshiv]

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.

 

[NARAYANK991]

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

 

[prshiv]

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

 

[Wakeel]

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?