Hi guys,
I'm creating a file that allows me to automatically determine the number of items to assign to a person depending on their current bucket load. I'm having trouble looking for the code/formula to insert that will allow me to do what I'm looking for.
Here is my sheet:
http://www.mediafire.com/file/fajjwnwstvdnzyt/Test.xlsm
Here is the code where it'll be inserted:
What I want to do is assign more pending items to those with the lowest bucket contents to sort-of even them out with the others whose buckets are still full. For example (assuming I only have two agents), if I have a total of 5 new items and 'Agent A' just has 1 item remaining in her bucket, I need to able to check what 'Agent B's bucket (2) contains and assign them 3 and 2 new items, respectively.
The goal is for the agent with the least amount in her bucket to get more of the new pending. Had Agent B had 3 remaining items in her bucket, the new task assignment should be 4,1 respectively. I need to be able to do this calc for all the agents' name that'll be listed on range B8:B100.
Right now, I'm stuck with manually assigning the names one by one and adjusting accordingly. I'm no good with math, so this takes forever. Any kind of help will be appreciated.
Thank you!
PS. The items on the right hand side are sample data that I use to check if my code is working properly.
I'm creating a file that allows me to automatically determine the number of items to assign to a person depending on their current bucket load. I'm having trouble looking for the code/formula to insert that will allow me to do what I'm looking for.
Here is my sheet:
http://www.mediafire.com/file/fajjwnwstvdnzyt/Test.xlsm
Here is the code where it'll be inserted:
Code:
Sub PasteandSortbyPrio()
Range("B8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("B8:E100").Sort Key1:=Range("D7"), Order1:=xlAscending
End Sub
What I want to do is assign more pending items to those with the lowest bucket contents to sort-of even them out with the others whose buckets are still full. For example (assuming I only have two agents), if I have a total of 5 new items and 'Agent A' just has 1 item remaining in her bucket, I need to able to check what 'Agent B's bucket (2) contains and assign them 3 and 2 new items, respectively.
The goal is for the agent with the least amount in her bucket to get more of the new pending. Had Agent B had 3 remaining items in her bucket, the new task assignment should be 4,1 respectively. I need to be able to do this calc for all the agents' name that'll be listed on range B8:B100.
Right now, I'm stuck with manually assigning the names one by one and adjusting accordingly. I'm no good with math, so this takes forever. Any kind of help will be appreciated.
Thank you!
PS. The items on the right hand side are sample data that I use to check if my code is working properly.