• 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

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

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

NARAYANK991

Excel Ninja
Hi ,

What will be the maximum number of names possible ?

What will be the maximum number of policies possible ?

Narayan
 

NARAYANK991

Excel Ninja
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

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

Excel Ninja
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

Rathank

New Member
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?
 
Top