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

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?

All comments

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

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

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

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

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

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

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)

=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.

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)

Thanks again Genevieve - really appreciate your help