How to Group Multiple Members into Their Corresponding Team

SOYBEANWAX

Hello folks!

I have a simple sheet with two columns: Supporting_Members and Team. The Supporting_Members column is a multi-select with contact list as the column type.

• PersonA and PersonB are from the Ops team.
• PersonC and PersonD are from the Finance team.

I used this formula for the Team column:

=IF(AND(CONTAINS("PersonA@example.com", JOIN(", ", [Supporting_Members]@row)), CONTAINS("PersonB@example.com", JOIN(", ", [Supporting_Members]@row))), "Ops", "none")

However, I'm getting "none" when it should be "Ops". I suspect this has to do with the column type, which is a contact list for the Supporting_Members column.

Any other ideas why I'm not getting the expected outcome?

jmyzk_cloudsmart_jp

Hi @SOYBEANWAX

After reading your question, I came up with the demo solution as follows.

https://app.smartsheet.com/b/publish?EQBCT=7e45824892224b8fbc0275710f052903

The first formula below uses the HAS function to determine if the Member has either A or B, then if so, return "Ops". And joining it with CHAR(10) and the similar formula for C or D, adding "Finance".
For example, the second row's Member has app(or A) and jmiyazaki(or C), so the formula returns Ops and Finance.
Here, I used the HAS to compare a Contact with another Contact.

The second formula uses the CONTAINS function. This function checks if a text value is contained in another text value. So, I used the JOIN function to create a text value from the Member@row multiple Contact value.

Specifing member is the formula

[Team] =IF(OR(HAS(Member@row, A#), HAS(Member@row, B#)), "Ops") + CHAR(10) + IF(OR(HAS(Member@row, C#), HAS(Member@row, D#)), "Finance")

[Team by CONTAIN] =IF(OR(CONTAINS(A#, JOIN(Member@row)), CONTAINS(B#, JOIN(Member@row))), "Ops") + CHAR(10) + IF(OR(CONTAINS(C#, JOIN(Member@row)), CONTAINS(D#, JOIN(Member@row))), "Finance")

Using lookup table

[Member 1] =IF(COUNTM(Member@row) = 1, Member@row, LEFT(Member@row, FIND(",", Member@row) - 1))

[Member 2] =IF(COUNTM(Member@row) = 2, MID(Member@row, FIND(",", Member@row) + 1, LEN(Member@row) - FIND(",", Member@row) - 1))

[Team by Table] =JOIN(COLLECT({Team List : Team}, {Team List : Supporting_Members}, OR(CONTAINS([Member 1]@row, JOIN(@cell)), CONTAINS([Member 2]@row, JOIN(@cell)))), CHAR(10))

The formula at the Using lookup table looks up a table like the one below.
First, we get the text value of the first and second Contact in the Member multi-contact value. (Member 1, 2)
Then, using those values, the third formula joins the collection of teams in the table below whose supporting members's joined text values contain either Member 1 or 2.

I prefer the latter method, but if we have more than two members in the Member column, getting a third or more Members will become more complicated.

https://app.smartsheet.com/b/publish?EQBCT=caa5725950ce44f0bbe58def9b6bf1a2

Lastly, though you did not ask in your question, I created a sheet to look up the team someone belongs to using the table above.

https://app.smartsheet.com/b/publish?EQBCT=0d8f37d2ba5f4fcea06933612034dfbc

I used the JOIN(@cell) formula in the second formula to join each row's supporting member. This is the first time to use the JOIN(@cell) formula, but it seems to be working as expected.