Trying to set up a Round Robin for techs based on project type and if they are not out on leave

BenAldrich

Hello I have been searching for a way to auto-assign a tech in a round robin flow based on what type of project it is . i.e only persons A-N should be assigned to the following types

Decom, Darken

While persons O-U should only get assigned to (or possibly any project type above or below)
Go-To, Downsize, Refresh, Build Out, Other

One variable would be if there is a person on a special project or out on leave, I need a way to pause assigning them.

Tech list A-U is just for reference here can be ID'd in any format

Tech	UC Tech Name
A	Jeff B
B	Fred G
C	Pavan K
D	Jaime K
E	Mike Me
F	James W
G	Allen B
H	Lynn G
I	James C
J	Chase H
K	Tim M
L	Mike Z
M	Tim
N	Brian T
O	Robert L
P	Jason L
Q	Santos H
R	Mike Mus
S	Neil H
T	Lori S
U	Mike McG

Here is the example in the sheet with sensitive columns hidden.

Thank you for your help!

Adam Costello

This is an interesting problem. I'm still working on it but you can use the below formula to assign a number (1-21) round-robin style to a project based on the request ID (which I assume is an auto # column). From there you can use that number to pull a name from your tech list on another sheet. Still working on the best way to only include certain people on certain projects and avoid it selecting certain techs if they're away without it changing the assignments once they return…

I chose 21 because you have techs A-U but if you have more or less just replace the 21 with however many you have.

=VALUE(RIGHT([Request ID]@row, LEN([Request ID]@row) - FIND("-", [Request ID]@row))) - FLOOR(VALUE(RIGHT([Request ID]@row, LEN([Request ID]@row) - FIND("-", [Request ID]@row))), 21) + 1

And I'm also trying to figure out a way to do it based on the project type. But I figured this was a good start

BenAldrich

Thank you for this. I assume you are saying to add this formula to the main sheet in a helper column (Tech ID) then on the other sheet I will have the list of techs and ID's 1-21 (in place of a-u). On the main sheet I will then enter

=INDEX({UC Assignment Tech}, MATCH([Tech ID]@row, {UC Assignment Tech ID}, 0))

This is getting the names to auto-populate on my sheet as described however if I change one of them on the 2nd sheet it changes it on the main smartsheet.

Hopefully, there is a way to do the original request or at least have it index the correct tech based on the project type.

Thank you!

BenAldrich

Could it work with the formula I had on the main sheet in column AV UC Team Assignment

=INDEX({UC Tech}, MATCH([Tech ID]@row, {UC Tech ID}, 0)))

but add references to the other sheet to look up like, if Tech ID at row and Type 1 or Tech ID and Type 2 or Tech ID and Type 3 or Tech ID and Type 4 or Tech ID and Type 5 or Tech ID and Type 6 or Tech ID and Type 7 match Tech ID (on 2nd sheet) insert UC Tech in AV UC Team Assignment? (Not sure how to write this formula)

I know this does not solve for how to handle vacation techs but it does for the assignment part.

Maybe there is a manual entry that can be entered on the sheet in an alt column when vacation in forcing a skip.

Thoughts

Jgorsich

Like others have said, interesting problem.

Can any of your techs do ALL (or an atypical subset) of problem types? If so, they risk getting overloaded by a system that doesn't account for that and adds them to the round-robbin twice.

My first thought is that you really need a few more columns (or sheets) added to your tech tracking side of it - a column with a 1-day-per-row listing of tech name and vacation dates, for example, would greatly aid the matter. And, if you DO have techs with an atypically wide scope (like 1 person that can handle Darken, Go-To, and Downsize), you need a more robust capability tracking list than just an ordered list of names as well - like a column per activity and which techs can do it. You'd also need a list of all tech names with a counter of how many projects they are currently assigned too.

If you had that, then you could check your new projects page and do a collect() for the names of techs that can handle that particular project AND are available on the days when the project needs to be done (these would be nested and would select your qualifying techs for assignment) and a second collect() of the project count per qualifying tech and index() to the first one that matches the minimum project count of the qualifying techs (this is what would accomplish the round robin).

Hopefully that makes sense - I could try to make you an example, but the number of required screenshots would not be small.

BenAldrich

So I was able to get my primary sheet to populate valid round robin style #'s by adding an additional column on my 2nd sheet called "Status" with ACTIVE or INACTIVE in the rows and was able to successfully get ID's assigned however I had to use 1-14 then 15-21 with the formula

=INDEX(COLLECT({Tech ID Column}, {Status Column}, "Active", {Tech ID Column}, IF(OR([Project Type]@row = "Decom", [Project Type]@row = "Darken"), <= 14, >= 15)), MOD(VALUE(RIGHT([Request ID]@row, LEN([Request ID]@row) - FIND("-", [Request ID]@row))) - 1, COUNTIF({Tech ID Column}, IF(OR([Project Type]@row = "Decom", [Project Type]@row = "Darken"), <= 14, >= 15))) + 1))

Then I tried to make it dynamic so it did not have to rely on the 1-14 and 15-21 by adding a column to my 2nd sheet called group and having either "Decom/Darken" and "Other Projects", I tried to make the formula on my primary sheet

=INDEX(COLLECT({Tech ID Column}, {Status Column}, "Active", {Group Column}, IF([Project Type]@row = "Decom" OR [Project Type]@row = "Darken", "Decom/Darken", "Other Projects")), MOD(VALUE(RIGHT([Request ID]@row, LEN([Request ID]@row) - FIND("-", [Request ID]@row))) - 1, COUNT(COLLECT({Tech ID Column}, {Status Column}, "Active", {Group Column}, IF([Project Type]@row = "Decom" OR [Project Type]@row = "Darken", "Decom/Darken", "Other Projects"))))) + 1)

or

=IF(COUNT(COLLECT({Tech ID Column}, {Status Column}, "Active", {Group Column}, IF(OR([Project Type]@row = "Decom", [Project Type]@row = "Darken"), "Decom/Darken", "Other Projects"))) = 0, "", INDEX(COLLECT({Tech ID Column}, {Status Column}, "Active", {Group Column}, IF(OR([Project Type]@row = "Decom", [Project Type]@row = "Darken"), "Decom/Darken", "Other Projects")), MOD(VALUE(RIGHT([Request ID]@row, LEN([Request ID]@row) - FIND("-", [Request ID]@row))) - 1, COUNT(COLLECT({Tech ID Column}, {Status Column}, "Active", {Group Column}, IF(OR([Project Type]@row = "Decom", [Project Type]@row = "Darken"), "Decom/Darken", "Other Projects"))))) + 1))

Both end up with #UNPARSEABLE

If I can make this work it would function for what I need. Any thoughts on this path?

Jgorsich

In the first new formula you've tried you've got your "or" criteria in the wrong place.

It isn't "= if ( criteria 1 OR criteria 2, True, False)"

but "=if(or(criteria 1, criteria 2), True, False)"

try:

=INDEX(COLLECT({Tech ID Column}, {Status Column}, "Active", {Group Column}, IF(or([Project Type]@row = "Decom",[Project Type]@row = "Darken"), "Decom/Darken", "Other Projects")), MOD(VALUE(RIGHT([Request ID]@row, LEN([Request ID]@row) - FIND("-", [Request ID]@row))) - 1, COUNT(COLLECT({Tech ID Column}, {Status Column}, "Active", {Group Column}, IF(or([Project Type]@row = "Decom",[Project Type]@row = "Darken"), "Decom/Darken", "Other Projects"))))) + 1)

BenAldrich

With a combination of support from here as well as ChatGPT I was able to piece together a solution. I will come back and post what I ended up doing later.

Thanks to all who responded.