What formulas help join people, teams, AND roles (person may have >1 team and >1 role)?

Lisa R

Stephen Covey says begin with the end in mind. I want to create views of people by scrum team and role as well as by role and scrum team (end result mock-up pictures below). It would be easy if EACH PERSON was only on 1 TEAM and only had 1 ROLE, however, there are other scenarios such as: 

* 1 person on 2 or more TEAMS as 1 ROLE

* 1 person on 1 TEAM as 2 or more ROLES

* 1 person on 2 or more TEAMS as 2 or more ROLES

* Each team should only have one of a few ROLES (like PO, SM, EM), but may have 1 to ~10 Engineers and 1 to ~5 QA

People by Team and Role.PNG

People by Role and Team.PNG

I have a PEOPLE, TEAMS, ROLES, and GROUP sheets with some basic data - mostly the names of PEOPLE, names of TEAMS, names of ROLES and names of GROUPS.

What I can't figure out is what columns to put where - to JOIN the people with teams and roles so it can pulled into the end result sheet - and do it in a way that will be efficient.

Does that make sense? If there is a different and/or better way to think about this, please share. Any help would be GREATLY appreciated!  

Genevieve P.

Hi @Lisa R

There's no way to parse the data out into different rows, if that's what you mean by line? However you can use CHAR(10) instead of a comma to create a line break (within the same cell) so each value moves below the previous one. Would that work for you?

=JOIN(COLLECT({NameRange}, {TeamRange}, Team@row, {RoleRange}, PM$1 = PM$1), ", " + CHAR(10))

Then use Wrap-Text on the entire column to set the formatting so each option shows beneath the other in the cell.

In regards to then finding out how many values are returned, the way that you've come up with is actually a good way to do this if you're looking to reference the Joined together values as the source.

The alternative would be to use a separate cross-sheet COUNTIFS formula looking into the source sheet without referencing the JOIN(COLLECT.

Ex:

=COUNTIFS({TeamRange}, Team@row, {RoleRange}, PM$1)

This should return a count of the number of rows that meet this criteria. Then your JOIN(COLLECT is bringing together the values from the Name Range column of those rows, but for the COUNT it's not relevant. Would this be better for you?

Mark Cronk

Hi Lisa,

What if you put it all in 1 sheet and used reports to get different views?

Your sheet would have columns for People, Team, Role, and Group. Your rows would be all of your data.

Create reports with filters on person, team, group and role.

Work for you?

Mark

Genevieve P.

Hi @Lisa R

If Mark's suggestion won't work for you, would you be able to post a screen capture of the source data that you're pulling this information from? (But block out any sensitive information).

It would be helpful to see the structure of this:

"I have a PEOPLE, TEAMS, ROLES, and GROUP sheets with some basic data"

Thanks,

Genevieve

Lisa R

Mark,

Thanks for the response. I have them in one table already, but wasn't sure if this was the best design. I am having trouble with the index, match, match formula. Any suggestions? The formula works for the PO column, but is throwing an error for the SM column.

index-match-match error.PNG

index-match-match PO-works.PNG

index-match-match SM-does not work.PNG

index-match-match table.PNG

Genevieve P.

Hi @Lisa R

Instead of an INDEX(MATCH(MATCH, try using INDEX(COLLECT to define two criteria. The Collect function will filter down the rows in the Name Range based on the criteria listed after the other columns, like so:

=INDEX(COLLECT({Name Range}, {Team Range}, Team@row, {Role Range}, PO$1), 1)

Let me know if this works for you!

Cheers,

Genevieve

Lisa R

Thanks Genevieve - that worked for use cases where a team has only one person for each role, but I get the #UNPARSEABLE error when a team has multiple people that fill a role. For example, most teams have more than one "Eng" role. Is there a way to account for this in the formula?

More than one eng per team.PNG

Genevieve P.

Hi @Lisa R

Of course, my apologies! We'll want to use a JOIN function instead of INDEX, in this case. It will Join together all the names of the people that are assigned to the same role and team.

Try this:

=JOIN(COLLECT({Name Range}, {Team Range}, Team@row, {Role Range}, PO$1), " / ")

You can change out what value separates your names by adjusting what's in the quotes at the end of the formula... ex: " , " for a comma, or " - " for a dash.

Cheers,

Genevieve

Lisa R

Thanks again Genevieve - the command you shared didn't work, but I was able to modify slightly and get it to work.

=JOIN(COLLECT({NameRange}, {TeamRange}, Team@row, {RoleRange}, PM$1 = PM$1), ", ")

For when there are multiple values per role, are there any characters/escape sequences that can be used to force each new value to a new line?

Also, is there a simpler way to count the number of values that were returned? I created a formula that I think covers all use cases, but it seems like there should be a better way.

Count Engineers per Team.PNG

=IF([Team1]7 <> "", (LEN([Team1]7) - LEN(SUBSTITUTE([Team1]7, ", ", ""))) / 2 + 1, 0)

Genevieve P.

Hi @Lisa R

There's no way to parse the data out into different rows, if that's what you mean by line? However you can use CHAR(10) instead of a comma to create a line break (within the same cell) so each value moves below the previous one. Would that work for you?

=JOIN(COLLECT({NameRange}, {TeamRange}, Team@row, {RoleRange}, PM$1 = PM$1), ", " + CHAR(10))

Then use Wrap-Text on the entire column to set the formatting so each option shows beneath the other in the cell.

In regards to then finding out how many values are returned, the way that you've come up with is actually a good way to do this if you're looking to reference the Joined together values as the source.

The alternative would be to use a separate cross-sheet COUNTIFS formula looking into the source sheet without referencing the JOIN(COLLECT.

Ex:

=COUNTIFS({TeamRange}, Team@row, {RoleRange}, PM$1)

This should return a count of the number of rows that meet this criteria. Then your JOIN(COLLECT is bringing together the values from the Name Range column of those rows, but for the COUNT it's not relevant. Would this be better for you?

Lisa R

Thanks again Genevieve - really appreciate your help

Genevieve P.

No problem! 🙂