Ideas on Randomizing Assignments

Michael Wilkesen

Good day community!

I have a complex request and am looking for some ideas / collaboration.

I am managing a project where we have dozens of requests being submitted daily to our very small team. I have successfully found a way to randomly assign new requests each team member as they are submitted (best solution for SS is a Round Robin approach, if you have other ideas, I would welcome them as well). What I am trying to accomplish now, is taking that assignment list, which at this point contains about 100 per associate, and limiting their work queue/view to only 10. Once a request has been completed/closed, then I want SS to replace that request with a new assignment. The goal is to consistently replenish the list to a max number of 10. I know this will probably need to consist of moving rows from one sheet to another based on some sort of ranking parameter.

I welcome any and all ideas and I am open to discussing ideas 1:1 anyone has accomplished something similar.

Genevieve P.

Hi @Michael Wilkesen

I would use a helper column in your main source sheet to number each row based on when the row was created (using an Auto-Number column), and numbering per user, by status (ex. when it's not "Closed").

Then you can use a Report to filter by this helper column and only show 10 rows up to number 10.

For example, this is the formula I'd use:

=IFERROR(RANKEQ(Auto@row, COLLECT(Auto:Auto, [Assigned To]:[Assigned To], [Assigned To]@row, Status:Status, <>"Complete"), 1), "")

It would create a number in my "Order" column, if the row is not complete:

Now I can use this in a Report, saying in the Filter that the Order Number should be less than 11, and Grouping by who it's assigned to:

As long as your users are shared to the underlying sheet and to the Report, they'll be able to make their updates directly in the Report. When they mark something as closed, then refresh the report, the row will disappear and a new one will take its place (because of the formula).

For example, I closed Task 5 in my group above. It disappears, and now I have a new task at the bottom, task 26:

Will this work for you?

Cheers,

Genevieve

Michael Wilkesen

@Genevieve P.

Thank you for the detailed feedback, I think this solution might just do the trick! I will build it out and give you more feedback ASAP.

Michael Wilkesen

Happy Monday @Genevieve P. I hope you had a great weekend.

So your suggestion works PERFECTLY! Thank you for the assistance. I have been working on taking this information to the next level, but I am running into a small snag that I hope you can see where I need to tweak my formula.

What I am trying to accomplish:

• I want to rank assignments based on which "age" category the submission falls into (e.g. <45 days old, 45-59 days, 60-89 days, etc.). I want each category rank to begin with 1 so that I can use report filters to have a nice proportion of category ages automatically assigned (e.g. <45 days: assign rank 1-10, 45-59 days: assign ranks 1-5, etc.). The rational is to be able to have the team assigned to new tasks as they are submitted, while still being assigned tasks from the various backlogs on this project.

Barrier

• When I try to manipulate your recommended formula to rank the categories, the ranking does not "restart" with 1.

I may be making things harder than they need to be, and I may be pushing Smartsheet limits, but I would be remiss if I did not try. Below are screenshots of my sample sheet, the formula to rank the entire worklist (RAND RANK Order) using the formula you provided, and the formula I am using to try to sub-rank each age category (RANK PC <45, RANK PC 45....) respectively. Any advice is greatly appreciated.

Genevieve P.

Hi @Michael Wilkesen

I'm so glad the initial suggestion is working for you!

In regards to your other formulas, I believe this is because we're not adding in the checkmark criteria into the COLLECT part of the formula. This means that the Ranking is still the same, it's just returning blank if the checkbox is not checked, instead of using this as one of the criteria.

Can you try adding in the additional range and criteria in the COLLECT?

After:

... Health:Health, <> Resolved", [Perceptive Content Age: 45 Days]:[Perceptive Content Age: 45 Days], 1), 1)))...etc

Does that make sense? You'll want to add this criteria to your formula as well.

Cheers!

Genevieve

Michael Wilkesen

Thanks @Genevieve P.!! As expected, your trick worked perfectly! Thank you for the extra set of eyes, once I read your suggestion, I knew instantly what I was overlooking. You are the best!!

Michael 😀

Genevieve P.

My pleasure, @Michael Wilkesen! I'm glad I could help. 🙂