Display a name from a cell range, round-robin

Another day, another Google Sheets (Excel-like) challenge.  :)

This time, I have a column of team names. The team names may or may not have an entry code associated with their team.

I spent nearly 2 hours searching the internet for answers. It seems that no one has tried to do this with just formulas. I found a few examples, but they required scripting. :(


The challenge is that I wish to automatically assign a reviewer (from Reviewer List column) if the team has an entry code. But in doing so, I want to assign a reviewer round-robin style, so that each reviewer is assigned approximately an equal number of teams.

Please see example sheet:
https://docs.google.com/spreadsheets/d/1Q2nN-MVD_iwJi2Se-PFWcNbMGb-EF6gQWQCruhnWW-Q/edit#gid=964764875

The way I am currently doing this isn't very smart, names are not used sequentially (and round-robin), every time there is a value in the Shared Entry column. What I wish to see is what is shown in the Expected Results column.

But to accomplish this challenge, it will need to work in Google Sheets, use only formulas, and work without add-ons.

Is this even possible?

Thank you so much
:)
LVL 4
S ConnellyTechnical WriterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
Are you willing to list your reviewers in reverse order? If so, put the following formula in cell F2 and copy it down:
=if($B2="","",lookup(min(arrayformula(countif(F$1:F1,$G$2:$G$7))),arrayformula(countif(F$1:F1,$G$2:$G$7)),$G$2:$G$7))

Open in new window

byundtMechanical EngineerCommented:
Without need to reverse the order of reviewers, I put this formula in cell E2 and copied down:
=if($B2="","",index($G$2:$G$7,match(min(arrayformula(countif(E$1:E1,$G$2:$G$7))),arrayformula(countif(E$1:E1,$G$2:$G$7)),0)))

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
S ConnellyTechnical WriterAuthor Commented:
Exceptional answer. Thank you, Byundt! :)
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

S ConnellyTechnical WriterAuthor Commented:
Pure brilliance!
S ConnellyTechnical WriterAuthor Commented:
Hello byundt,

Thank you again for that elegant formula. :)

I am trying to decypher the formula to try to understand it a bit better. Can you explain it a bit?

I would like to make a minor modification so that instead of being triggered by any value in the Shared Entry column, only values beginning with ASC will trigger a name.

I originally thought that I could just change,
=if($C4="","",
to
=if($C4="ASC*","", but clearly I am not understanding how this works. :(

Thank you. :)
byundtMechanical EngineerCommented:
I don't believe the IF function takes wildcard characters like *. It doesn't in Excel.

In Excel, you may use wildcard characters in COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, MAXIFS, MINIFS, HLOOKUP, MATCH, VLOOKUP. I haven't tested whether the equivalent functions in Google Sheets may use wildcard characters, but think it likely.

You should be able to use =IF(LEFT($C4,3)<>"ASC","", balance of formula)
S ConnellyTechnical WriterAuthor Commented:
Thank you, again. Another brilliant answer! :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Google

From novice to tech pro — start learning today.