TEXTJOIN with Multiple Conditions

Jer2224

New Member
Joined
Nov 20, 2009
Messages
39
Hi all,

Using Excel for Office 365 MSI 16.0.11 x64 version 1902.

I have a scenario where I think TEXTJOIN would work, but I can't figure out how to concatenate several IF statements together.

I have:
- Column A (id) with hundreds of row entries, simple numbers 1-.....
- Column B (category) with a data validation list of 10 or so entries in every cell.
- Column C (review) with free text fields.
- Column D (related).

What I am trying to solve is a formula for column D which first looks for specific free text in column C (review), and if it finds "review" then looks up and returns all the "id" entries from column A for the specific list entry in B, minus it's own. When no entries are found, n/a is returned.

Example:

1 apples review 3, 6
2 oranges old n/a
3 apples review 1, 6
4 apples old n/a
5 oranges new n/a
6 apples review 1, 3
7 bananas new n/a

I have tried several combinations of IF, SUMIFS, and other functions but am not having any luck. I also don't know VBA so don't know how to code that yet.

Would be grateful for any pointers.
Many thanks.
Jer2224.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Try this:

=IF(C1="review",TEXTJOIN(",",TRUE,IF($B$1:$B$7=B1,IF($C$1:$C$7="review",IF(ROW()<>ROW($C$1:$C$7),$A$1:$A$7,""),""),"")),"")

Needs CSE.
 
Upvote 0
After you put that CSE formula (CSE mean Control+Shift+Enter)

press the keys Control+Shift+Enter simultaneously
 
Last edited:
Upvote 0
After you put that CSE formula (CSE mean Control+Shift+Enter)

press the keys Control+Shift+Enter simultaneously

Got it - thanks.

Looks much more like what I am trying to achieve - thank you.

One small point is that in Column C, there might be several other words relating to subsequent follow-up actions, so a cell might look like "review, alert, meeting, regular" (all as manual but regular repeatable expressions for the cell) all as plain text. What this formula needs to do is check that "review" is one of the words and if so then report the other instances. Make sense? :-)
 
Upvote 0
Try this instead

=IF(ISNUMBER(SEARCH("review",C1)),TEXTJOIN(",",TRUE,IF($B$1:$B$7=B1,IF(ISNUMBER(SEARCH("review",$C$1:$C$7)),IF(ROW()<>ROW($C$1:$C$7),$A$1:$A$7,""),""),"")),"")
 
Upvote 0
Thanks - I will have a play around with this and report back. Many thanks for your assistance so far! Cheers. Jer2224.
 
Upvote 0
Hi. So have plugged in the formula you sent, and what seems to be happening is everytime "review" is found in column C, every row entry in column A is being concatenated into the formula result in column D. Where as I need the formula to find "review in column C, then return all the column A entries for the content of column B. Make sense?

For example, if row B is apples and row C is "review", then I need the formula to return all the other row numbers for A where row B is apples and row C is review.

Cheers.
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

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