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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Forum statistics

Threads
1,223,896
Messages
6,175,264
Members
452,627
Latest member
KitkatToby

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