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.

 

[dconnors]

@Fluff I think the role names is messing up the formula you showed me. When I created test data I just added Roles 1-10 because we have 10 different roles for the account. However, they don't actually have numbers so I'm guessing that the isnumber formula was referencing that and since the actual roles don't have numbers it's breaking.

I'm not able to adjust the formula because I don't understand the search function well enough. I also found out we only need one role per search so I adjusted the formula for that. I tried a few options and none of them worked.

Here is a formula in the first row of the table that always works with the actual role, the same formula works for any other role we have as long as it's in the first row of data.
=TEXTJOIN(",",,FILTER('Relationship Report'!$D$5:$D$10003,('Relationship Report'!$D$5:$D$10003<>$L$1)*('Relationship Report'!$B$5:$B$10003=$B7)*((ISNUMBER(SEARCH("Beneficiary - Primary",'Relationship Report'!$E5:$E10003)))),""))

Here is a formula in one of the subsequent rows for a different role that does not work.
=TEXTJOIN(",",,FILTER('Relationship Report'!$D$5:$D$10003,('Relationship Report'!$D$5:$D$10003<>$L$1)*('Relationship Report'!$B$5:$B$10003=$B8)*((ISNUMBER(SEARCH("Trustee",'Relationship Report'!$E6:$E10004)))),""))

I've been looking at this for about 8 hours this week with no success but I think as I was writing this I at least got to the point that I think it's the isnumber function, which after reviewing the comments from Rob above I should have caught immediately.

Unfortunately it's all client data so I can't share the spreadsheet so I understand if you're not able to give any direction on this.

Thanks again.

 

[Fluff]

If all search terms work for the 1st row, but not the 2nd then it sounds as though the value in B8 is not found on the other sheet.

 

[dconnors]

B8 (the name of the account) is listed on the Relationship Report in cells B1498-B1500 so it is there. However, looking at that again I did see that I hadn't made the references! I knew it had to be something easy I missed.
Thanks again for your help.

 

[Fluff]

Glad you sorted it & thanks for letting us know.