• 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.

Evenly Assign Items To Available Employees

T

Thomasray123

New Member
Hello! I have a spreadsheet with a list of items each day pertaining to cases employees work. Each case has an assigned owner but cases pile up and need to be evenly distributed.

I’ve attached a dumbed down version of my spreadsheet without sensitive information.

I need to assign the list evenly amongst the available employees (for example this is located on a separate worksheet but I may change this to be a user form with checkboxes at a later date).

-Clients with multiple cases should be assigned to the same user regardless of if it would put them over the evenly distributed amount.
-Late Stage cases should always go to the case owner if available and if they haven’t reached the evenly distributed number.
-Cases over $500 would follow in priority.

Example and priority list is on another worksheet for reference. If user 1 is available and has 30 late stage cases and there’s 5 available users and 100 cases, user 1 should get 20 late stage cases of their own. Sorry if this is a bit confusing but I’m rattling my brain on how to accomplish this!
 

Attachments

  • SampleItems.xlsx
    23.5 KB · Views: 27
Thomasray123
Are You sure that You've used same terms with Your writings and Your data?
You've given some examples ... but do those match with Your data?
What would Your expected result?
... and how would it look in that file (add eg with red text)?
 
Sorry about that! An possibility of the expected result is attached. Late Stage cases and cases where the amount is over $500 go to the same Owner if they are available that day. Names that are duplicated (multiple cases for same person) get grouped and assigned to only one employee rather than splitting it up (in the example this isn’t much of an issue but sometimes a person can have many cases. So if there were 5 available employees, 100 cases. But 60 of those cases were for one person one employee would get 60 cases and the other 4 would get 10 each)

I hope that makes a little more sense.
 

Attachments

  • SampleItems2.xlsx
    25.2 KB · Views: 14
Thomasray123
I searched (tried) to find red texts ... if found ~16.
Maybe needs more time to find my needed... hmm?
... and: So if there were 5 available employees, 100 cases. But 60 of those cases were for one person one employee would get 60 cases and the other 4 would get 10 each

Maybe soon I'll understand Your sentence:
Sorry if this is a bit confusing but I’m rattling my brain on how to accomplish this!
 
The following formula would get me the tasks assigned to the owner but it doesn't limit it to the evenly distributed number and I still would need to randomly assign the rest of the items after this is completed. I adjusted the example text on the tab to fit the data and also highlighted in red text what I would like to be the end result.

=IF(SUMPRODUCT((AvailableUsers!$A$1:$A$13=TaskAssigning!B2)*(TaskAssigning!M2="Late Stage")+(K2>500)),B2,"")

Duplicated names (Column H), Amounts > $500 (Column K), and Late Stage cases (Column M) are highlighted for visibility to see what I'd want the formula or macro to go off of. I wouldn't actually want these to be highlighted.

I hope this is a little less confusing-- I was on a time crunch between meetings earlier and just rushed something together.

Thank you!
 

Attachments

  • SampleItems3.xlsx
    24.9 KB · Views: 7
Thomasray123
hmm...?
Something ... something ... testing ... sample!
There is [ Do It ]-button near O1-cell ----
Press it! ... and let nerves rest ;)
You'll get Users to P-column!
Also ... Available Users has values in D-column.
( that 'animation-mode' can take away ... later )​
 

Attachments

  • SampleItems3.xlsb
    17.2 KB · Views: 38
I've run into one main problem. If the owner isn't listed on available users the cases with duplicate names won’t be assigned to the same user. Is there a way to adjust this so all cases with the same name get assigned to the same user.

Also, I put the assigned user to be in Column A but I think I adjusted the macro to where it didn't cause any extra issues. Also with the data in there now the last handful of cases don't get assigned out but I'm assuming this is because they've reached a limit?

Thank you!
 

Attachments

  • SampleItems4.xlsm
    30.8 KB · Views: 19
Thomasray123
I should be able to tinker with it to adjust it to the actual spreadsheet.
You should has given Your file as Your actual file. Later, differences could make ... a lot of modifications.

There cannot be 'any challenges'...
If the owner isn't listed on available users the cases with duplicate names won’t be assigned to the same user.
Did You give that kind of case with Your 'needs'?
It should be possible to take care, but do You have the master idea - how?

If You modify code or layouts then ... You should take care that it'll work as it should work.
I tested my code only with Your sent data ... then it works.
Your change from 'P' to 'A' needed more modifications - not same layouts!
Also find possible cause of that 'not assigned' case.

Your sent file has ... a lot of extras ... and I'm always use my version.

> If You have the master idea for new rule ... let me know and I'll check it.
 

Attachments

  • SampleItems3.xlsb
    29.8 KB · Views: 30
Fluff13 said:
Cross posted multiple sites
Click to expand...
The rules don't explicitly state not being allowed to cross-post. They say to add it to your post if you have cross posted but I don't see a way to edit it this post and it wasn't posted multiple places when I posted here. I'm more than willing to delete those other posts if it will affect this post. I'm sorry.
 
Thomasray123
As also written in rules:
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an answer elsewhere, have the courtesy of posting the answer here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
And You have asked in other forum to modify same #8 Reply ... challenge?
 
vletm said:
Thomasray123
As also written in rules:
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an answer elsewhere, have the courtesy of posting the answer here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
And You have asked in other forum to modify same #8 Reply ... challenge?
Click to expand...
I would post the answer here if received and as stated I can't edit my post-- only add replies. I deleted or edited the posts I was able to and the others I can't take down as there's no option.

vletm said:
Thomasray123
I should be able to tinker with it to adjust it to the actual spreadsheet.
You should has given Your file as Your actual file. Later, differences could make ... a lot of modifications.

There cannot be 'any challenges'...
If the owner isn't listed on available users the cases with duplicate names won’t be assigned to the same user.
Did You give that kind of case with Your 'needs'?
It should be possible to take care, but do You have the master idea - how?

If You modify code or layouts then ... You should take care that it'll work as it should work.
I tested my code only with Your sent data ... then it works.
Your change from 'P' to 'A' needed more modifications - not same layouts!
Also find possible cause of that 'not assigned' case.

Your sent file has ... a lot of extras ... and I'm always use my version.

> If You have the master idea for new rule ... let me know and I'll check it.
Click to expand...

I can't post the actual file due to policy issues unfortunately. For the assigning cases with duplicate name to the same person I've been trying to think of a way to do this. I must have missed adding an instance to test that. If you change User9 from available to unavailable all of the duplicates that would be assigned to him are divied out.

I've though about applying a unique number to each unique value so the duplicated names have the same number and try to figure out something off of that but I don't know how I would utilize that or if that's completely unnecessary. Loop functions in VBA are something I'm not very familiar with hence why I'm struggling. For now, removing the clearcontents option and manually assigning the groups out seems to be working although User9 is getting more than their share of items (21) as it's assigning out a larger amount of >500 and late stage.
 
Last edited:
Thomasray123
  • If you do cross-post, please put that in your post.
    • You've missed
  • Also if you have cross-posted and get an answer elsewhere, have the courtesy of posting the answer here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
    • You've gotten answers.
    • You also needs to do something with those other forums.
  • it wasn't posted multiple places when I posted here.
    • as I commented - after my reply #6 reply ... You've asked 'I've run into one main problem...' ... time flies or how?
  • Nobody won't need Your actual-file here - same layout!
Your 'one main problem' with this thread
... and that challenge is possible to solve, but ... do You know, who should have an idea?

What are You going to do? ... follow rules or what?
 
Hi guys...I have a similar request and looks like I can use the VBA in this file. Please forgive me if I am not supposed to ask but if I can, can you share the password of the VBA project? Please let me know if I need to make request for the same through some other channel. And again, please excuse (if I shouldn't be asking this) and thanks!
 
You must log in or register to reply here.
Back
Top