Textjoin with conditions - cell contains specific text

[dconnors]

I am trying to create a report we will run daily for a different individual each time (B10). We'll export a report that has about 7000 lines, a sample of which is shown in E2:G12 below.

I got farther than I thought (I can exclude the person the report is run for, B10) but now I am unable to figure out how to show data in column B only if column F CONTAINS "Role 3" or "Role 4" [the export is from a multi-picklist field] and column C only if column F contains "Role 1". The formula I'm using is in B6 and C6.

The data can have multiple people per account and multiple people with the same role but all roles for each individual should be on the same row, ie each individual should be listed once for each account.

The results I'm looking for are in B15:D19, cell C19 and D18 are blank.

Thanks for any help.

DeDe

I think it's listed somewhere but I'm on Microsoft 365 Version 2209.

On this computer I'm not able to install the add in for the mini sheet.

 

[Fluff]

How about

=TEXTJOIN(",",,FILTER($G$3:$G$12,($G$3:$G$12<>$B$10)*($E$3:$E$12=A3)*((ISNUMBER(SEARCH("Role 3",$F$3:$F$12)))+(ISNUMBER(SEARCH("Role 4",$F$3:$F$12))))))

 

[Fluff]

Forgot to mention, if you could have Role 10 or more, then the formula will need modifying.

 

[dconnors]

Fluff - that worked great in column B!

I tried to copy it to column C and change the role in the formula to Role 1 and remove the additional criteria and it doesn't work. I tried leaving both criteria in as well and changed both to "Role 1" with the same result.
It seems like I should be able to use the same formula because all the criteria are the same but the role but I'm missing something. I haven't used any of the functions you've added (FILTER, ISNUMBER, SEARCH) before so I likely need to do a bit of research to figure it out.

If you have any ideas I would appreciate any help, otherwise thanks so much for the formula, I would never have been able to do anything this complicated. I appreciate your help and willingness to help!

Here's the formula I updated...basically the same as the other column except for the "Role 1"

=TEXTJOIN(",",,FILTER($G$3:$G$12,($G$3:$G$12<>$B$10)*($E$3:$E$12=A3)*((ISNUMBER(SEARCH("Role 1",$F$3:$F$12))))))

 

[Fluff]

That formula should work for Role 1.
In what way isn't it working?

 

[dconnors]

I looked again and it is working in my test data, I was looking at the wrong field.

It's not working in my real data file, it's pulling the wrong roles so I'll troubleshoot that since it's working with test data.

Thank again, I really appreciate your help!

 

[Fluff]

Glad to help & thanks for the feedback.

 

[RobP]

@Fluff, I'm curious - what's the limitation of such formula can I ask, if there is a "Role 10 or more" for example ?

 

[Fluff]

The formula will return a true for Role 10 as it also includes Role 1

 

[RobP]

ah, good catch - got it.

thanks