Textjoin with conditions - cell contains specific text

dconnors

New Member
Joined
Nov 10, 2009
Messages
27
Office Version
  1. 365
Platform
  1. Windows
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.

Summary Screenshot.jpg
 
@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.
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.
 
Upvote 0
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.
 
Upvote 0
Glad you sorted it & thanks for letting us know.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top