Assigning people to tasks randomly and with a condition

David Boltz

Hello,

I would like to randomly assign service representatives tasks, but only those who meet the criteria of already completing a task previously assigned to them. I would like to create a workflow or formula that would assign individuals on their availability and at random. Any suggestions?

Genevieve P.

Hi @David Boltz

I have a few different ideas for how to automate assigning a person to your tasks, but it will depend on your current sheet set up. Would you be able to provide screen captures of your sheet and explain how your tasks are tracked? (But please block out sensitive data). How many service representatives do you have?

In one of my sheets I have two column formulas set up. One COUNTS the number of tasks that an assigned person has completed (or has put a date in). The other then finds the user with the MIN number of completed tasks and puts that name in the row.

Then you can use the value in that "Assign Next" column in your workflow to match up what user to put in the "Assigned To" column for the next new row created. (See: Assign People in an Automated Workflow)

However, this doesn't take into account any "in progress" tasks, or how recently the last task was closed.

For example, if Eric has 5 tasks open, but only 1 closed, he would be seen as the MIN:

So in this instance, and depending on your sheet set up, you may want to change up what you are counting. Perhaps you could COUNT how many assigned tasks are in Progress (or not Completed) instead, and assign the person with the lowest number of Open Tasks:

You can hide these helper columns once you have the correct formulas set up. Does this make sense? Would this work for you?

Cheers,

Genevieve

bking8

Hello I am currently wanting to transition my SharePoint Tasks over to SmartSheet due to the capabilities with auto assigning, I'd like to employ this option on a rolling basis. Essentially - assign the next case to the person with the least amount of "in progress/pending" cases. I'm following this thread but want to make sure I am creating the appropriate additional sheets.

On one sheet - will I need to create a column that has everyone that is possible to be assigned to, then add an additional column to "count" how many cases they have currently in progress/pending?

Genevieve P.

Hi @bking8

In the example above I only used one sheet with 2 Helper Columns that can then be hidden!

• One column Counts how many rows in the sheet are assigned to the Contact in the current row
• One column surfaces the name of the person with the least rows assigned for the Automation

bking8

@Genevieve P. Thank you for this - I am getting an #Unparseable message on the Count Column

My current columns and my formula

=COUNTIFS([Offline Advocate Assigned]:[Offline Advocate Assigned], [Offline Advocate Assigned]@row, In Progress: In Progress, )

Be advised - each line represents a case that had to go through a triage process with 1 advocate and then if escalated, was then assigned to another advocate. We are hoping to remove the triage process, but I first need to get this auto-assign process working.

Genevieve P.

Hi @bking8

Here's the structure of a COUNTIFS Function:

=COUNTIFS([Column 1]:[Column 1], "Criteria 1", [Column 2]:[Column 2], "Criteria 2")

So in your instance, it sounds like "In Progress" is your second criteria and the column you're looking in is called "Escalated Case Status". If that's correct, you'll want to set up your formula like this:

=COUNTIFS([Offline Advocate Assigned]:[Offline Advocate Assigned], [Offline Advocate Assigned]@row, [Escalated Case Status]:[Escalated Case Status], "In Progress")

Does that make sense?

bking8

Yes that makes sense @Genevieve P.! It worked, thank you so much! I've gotten the "index" formula to work as well but when I try to create the automation to assign the next person - it won't allow me to select more than one person.

Genevieve P.

Hi @bking8

Great! I'm glad the first part worked for you.

In regards to the Automation, you'll need to build this out with multiple conditions ending in multiple possible actions: one for each person that may appear in your "Assign Next" column:

See: Assign People in an Automated Workflow and Condition Blocks: Filter What Your Automated Workflows Send

Cheers,

Genevieve

bking8

@Genevieve P. thank you will try it. Is it common for the "assign next" column to show blank when it pulls up. I'm trying to test if the automation is working but my Assign Next Column is now blank and its worked before with populating names. I'm not.

Genevieve P.

Hi @bking8

I see you have a number of blank rows that have the COUNT as 0. This is the minimum of your Count column, so the formula is returning blank.

If you delete out those test/blank rows it should fill in the Assign Next column with a name instead. 🙂

Robert B

@Genevieve P.

Rather then add a new topic, thought I would add to this as it is related.

Was working on an auto-assignment process, and the above information solves for that splendidly.

The COUNTIFS and INDEX columns are working perfectly.

I would like to now include additional variables to this auto-assignment model.

Suggestions on how to be able to dynamically add/remove an individual from the auto-assignment process?

-For example if someone was out sick or on vacation I would like to be able to easily take them out of auto-assignment rotation during their time off, and then be able to easily plug them back into rotation upon their return to work. My mind goes to some sort of helper status column whereby the individual is either online or offline status, so this would trigger whether they are in rotation or not to be assigned to, this being pulled from a helper sheet where the manager could easily make the change of employee status.

Second question, is there a way to manipulate that auto-assignment to account for a difference in % allocation or some such.

-Situation is some of the assignees are on a full time basis while others may be part time, so would like the part-time employees to only be in rotation to receive a portion of the requests that come in. Example would be every ~10th request that comes in, or stated a different way, only be in rotation 10% of the time, some sort of easily manipulated value that accomplishes this.

Thank you in advance for the support!

-Robert

Genevieve P.

Hi @Robert B

The Assignment column is based around the minimum number in the Count column. This means that yes! We could include additional criteria into the Count column to adjust it based on your needs.

1) Adding / Removing individual:

I agree that a helper sheet in this instance would be useful. If you had all employees listed as a Contact down one column, then another column identifying if they are currently "Online" or "Offline", then yes, we can reference this.

I would add an IF statement in front of the current formula so it automatically adds a really high value to the cell if the current user is "offline" in the other sheet. For example:

=IF(COUNTIFS({Employee Column}, [Assigned To]@row, {Status Column}, "Offline") >= 1, 999999, COUNTIFS([Assigned To]:[Assigned To], [Assigned To]@row, Complete:Complete, "")

2) Adding weight to individuals

This is an interesting one. I think you would potentially need to use the same helper sheet with the Contacts listed, Offline/Offline listed, and then have another dropdown column identifying what type of employee they are, then use this to multiply their current COUNT by a specific percent, based on those values.

For example, lets say you have:

Full Time / Half Time / Minimal Time

• Your Full Time assignees could have the weight of 1 - each row counting as 1.
• Your Half Time assignees could have the weight of 2 - each row counts as double.
• Your Minimal Time assignees could have the weight of 10 - each row counts as 10, meaning a Full Time employee does 10 rows for each of the Minimal's 1 row

You would need to have set dropdown values associated with a set weight. Then once you've decided on your weight, we can use the same IF statement structure to check the current employee against the helper sheet and multiply the final COUNT by different values.

=IF(COUNTIFS({Employee Column}, [Assigned To]@row, {Status Column}, "Offline") >= 1, 999999, COUNTIFS([Assigned To]:[Assigned To], [Assigned To]@row, Complete:Complete, "") * (IF(COUNTIFS({Employee Column}, [Assigned To]@row, {Type of Employee}, "Half Time") >= 1, 2, IF(COUNTIFS({Employee Column}, [Assigned To]@row, {Type of Employee}, "Minimal Time") >= 1, 10, 1)))

Let me know if that makes sense and will be easily adjustable for you!

Cheers,

Genevieve

Robert B

@Genevieve P.

BRILLIANT! Both your solutions work great independently, as well as collectively.

Going to throw one more variable into the mix for your creative input.

If an employee is Out of Office 'Offline' for a few days or more, then they are taken out of rotation with the increased count '999999' added for them, this works perfect. Challenge I am running into, is when they return to work, and they are put back into rotation 'Online', their count reverts back to what they had when they went out. So in theory they will be running a much lower count then their peers and ALL the new tasks that come in will be assigned to them until they catch up to their peers count.

Only thing I can think of is a reset to get everyone on equal parity, starting from '0' or some other standard baseline #.

Or somehow manually/artificially manipulating that returning to work employees count so they are in line with their peers.

Appreciate the consideration. Thank you!

-Robert

Genevieve P.

Hi @Robert B

Glad to hear it's working for you!

For your most recent variable... I'm interested in hearing more about your process.

In the original COUNTIFS we're only counting rows that are blank in the "complete" column, meaning tasks that are not done:

COUNTIFS([Assigned To]:[Assigned To], [Assigned To]@row, Complete:Complete, "")

Let's ignore the weighted values for a minute and just focus on number of tasks.

You're absolutely correct - if I go on holiday and have 5 tasks that aren't complete, when I come back and I'm no longer "offline", I'll have a count of 5 again. Then, if my peers have 10 tasks each because they've been picking up more while I've been away... I'll be assigned the next 5 in a row until we're all filled up to the same capacity.

Is this incorrect behaviour? We could potentially use the COUNT to filter rows based on different criteria (e.g. "Status" or something else).

Dharmesh Popat

@Genevieve P. Thanks for all insights on this thread. Everything works when I test, however in my case new rows are added into my Sheet automatically and they are added in bulk at once. So let's say currently Assigned to Next column says "Person B" and there are 10 rows added, all 10 rows are auto-assigned to "Person B" however in reality the first few rows would go to "Person B" then there is a balance between "Person A" and "Person B" and then all new rows are equally distributed between "Person A" and "Person B" (they both are full 1 Resource).

The auto-assign workflow I have set is with a behavior of "When rows are added" and "When 'Assign Next' is 'Any Value'.

Can anyone help me here to set correct behavior for a process where there are multiple rows are added at once.

Genevieve P.

Hi @Dharmesh Popat

This is a tricky one! I have to admit I'm not sure if it will be possible to individually assign those 10 rows evenly if they're added at the exact same moment. If it's just 2 people, you could potentially keep the COUNT formula going to help you determine who to manually assign.