Allocating work based on sum of numbers

[vinayrajani]

Hi Team,

Could you please help me in creating VBA macro for allocating work based on sum of entry count to employees equally or based on % updated? Here employee & % varies daily. (File attached)

1. Daily the volumes we get is around 500 - 700 keys and each keys has varying entry counts. This volume definitely will vary each day. We are presently allocating work based on the count of keys (85) to employees equally or sometime based on % wise.

2. Above methodology is unfair as each employee will not get equal numbers to process as one will end up processing more or less entry counts. Productivity is calculated based on the entry count processed rather than key count.

3. I need a macro which can easily allocate based on the employee given equally considering the sum of the entry count. It would be great if we can also allocate work to employees based on the % we choose. Please let me know if you need more clarity so I can explain.

Thanks for helping in advance

Vinay

 

[r1c1]

Hi Vinay,

Welcome to Chandoo.org forums and thanks for posting your question.

You can a macro to approximately allocate the jobs to employees. I say approximately because when using running total method, we may not find exact solution to match the % requirements.

See the attached file. The macro looks like this:

Sub AllocateJobs()
    'approximately allocates the jobs in Column B based on the % distribution specified
    'in the employee data
   
    Dim emps As Range, emp As Range, job As Range
    Dim i As Long, allocated As Long, jobCount As Long
   
    Set emps = Range("employees")
    jobCount = Application.WorksheetFunction.Sum(Range("b:b"))
   
    Set job = Range("b2")
   
    For Each emp In emps
        allocated = jobCount * emp.Offset(, 1).Value / 100
        Do While (allocated > 0 And Len(job.Offset(i)) > 0)
            job.Offset(i, 1).Value = emp
            allocated = allocated - job.Offset(i).Value
            i = i + 1
        Loop
    Next emp
   
End Sub

You can also use formulas to get similar results. In the attached workbook you can find both approaches.

 

[vinayrajani]

Awesome It worked. I will let you know in case of any queries. You are really Great

 

[vinayrajani]

Hi Vinay,

Welcome to Chandoo.org forums and thanks for posting your question.

You can a macro to approximately allocate the jobs to employees. I say approximately because when using running total method, we may not find exact solution to match the % requirements.

See the attached file. The macro looks like this:

Sub AllocateJobs()
    'approximately allocates the jobs in Column B based on the % distribution specified
    'in the employee data
  
    Dim emps As Range, emp As Range, job As Range
    Dim i As Long, allocated As Long, jobCount As Long
  
    Set emps = Range("employees")
    jobCount = Application.WorksheetFunction.Sum(Range("b:b"))
  
    Set job = Range("b2")
  
    For Each emp In emps
        allocated = jobCount * emp.Offset(, 1).Value / 100
        Do While (allocated > 0 And Len(job.Offset(i)) > 0)
            job.Offset(i, 1).Value = emp
            allocated = allocated - job.Offset(i).Value
            i = i + 1
        Loop
    Next emp
  
End Sub

You can also use formulas to get similar results. In the attached workbook you can find both approaches.

Awesome It worked. I will let you know in case of any queries. You are really Great

 

[vinayrajani]

Hi Team,

Adding to the previous query, If I have some 10-20 tasks assignment specific to employees, can it be allocated based on the tasks already defined in column "E"?
For example, already Task 1 to Task 10 is defined in column E, now I need allocation based on % as well as tasks defined in column E which you will see in Scenarios based allocation table (from cell G19 onwards). Please find the attached file and let me know if you have any queries

 

[vinayrajani]

Hi Team,

Could you please help me in creating VBA macro for allocating work based on sum of entry count to employees equally or based on % updated? Here employee & % varies daily. (File attached)

1. Daily the volumes we get is around 500 - 700 keys and each keys has varying entry counts. This volume definitely will vary each day. We are presently allocating work based on the count of keys (85) to employees equally or sometime based on % wise.

2. Above methodology is unfair as each employee will not get equal numbers to process as one will end up processing more or less entry counts. Productivity is calculated based on the entry count processed rather than key count.

3. I need a macro which can easily allocate based on the employee given equally considering the sum of the entry count. It would be great if we can also allocate work to employees based on the % we choose. Please let me know if you need more clarity so I can explain.

Thanks for helping in advance

Could you please advise whether any feasibility is there on my previous query raised.

 

[QCARE]

Hi Vinay,

Welcome to Chandoo.org forums and thanks for posting your question.

You can a macro to approximately allocate the jobs to employees. I say approximately because when using running total method, we may not find exact solution to match the % requirements.

See the attached file. The macro looks like this:

Sub AllocateJobs()
    'approximately allocates the jobs in Column B based on the % distribution specified
    'in the employee data
 
    Dim emps As Range, emp As Range, job As Range
    Dim i As Long, allocated As Long, jobCount As Long
 
    Set emps = Range("employees")
    jobCount = Application.WorksheetFunction.Sum(Range("b:b"))
 
    Set job = Range("b2")
 
    For Each emp In emps
        allocated = jobCount * emp.Offset(, 1).Value / 100
        Do While (allocated > 0 And Len(job.Offset(i)) > 0)
            job.Offset(i, 1).Value = emp
            allocated = allocated - job.Offset(i).Value
            i = i + 1
        Loop
    Next emp
 
End Sub

You can also use formulas to get similar results. In the attached workbook you can find both approaches.

 

[QCARE]

Hi All,

I really like the allocation macro, however; I have more than 5 employees to allocate work to. How can I exand the employee from a-e to a-s. Thanks

 

[Arun130691]

Guys I just joined this forum and gone through the posts. It s really amazing and useful for all excel learner's.


I need now a macro for allocation of cases to the team.

1. Each user should get a individual spreadsheet of his/her cases
2. I have a specific column named "rate" which should not be allocated at the beginning so it should moved to a specific sheet in the workbook. A separate button should be available to distribute this data.
3. Cases should be assigned equally and a field is required to manually input the available of users daily so the macro allocated equally aaciding to that.


Can anyone please help me to do this !!!

 

[vletm]

Above reply is same as this:
https://chandoo.org/forum/threads/need-allocation-macro.40309/