Add value based on lowest cell contents

iSoleil

New Member
Joined
Oct 14, 2017
Messages
17
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:

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

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Forum statistics

Threads
1,223,214
Messages
6,170,774
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top