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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,644
Office Version
  1. 365
Platform
  1. Windows
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.
 

Jer2224

New Member
Joined
Nov 20, 2009
Messages
39
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.

Sorry - CSE? Can you clarify? Thanks so much for taking time to reply...... :)
 

Tom.Jones

Active Member
Joined
Sep 20, 2011
Messages
358
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
After you put that CSE formula (CSE mean Control+Shift+Enter)

press the keys Control+Shift+Enter simultaneously
 
Last edited:

Jer2224

New Member
Joined
Nov 20, 2009
Messages
39

ADVERTISEMENT

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? :)
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,644
Office Version
  1. 365
Platform
  1. Windows
So do you mean the cell contains review rather than is review?
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,644
Office Version
  1. 365
Platform
  1. Windows
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,""),""),"")),"")
 

Jer2224

New Member
Joined
Nov 20, 2009
Messages
39
Thanks - I will have a play around with this and report back. Many thanks for your assistance so far! Cheers. Jer2224.
 

Jer2224

New Member
Joined
Nov 20, 2009
Messages
39
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.
 

Forum statistics

Threads
1,148,006
Messages
5,744,315
Members
423,861
Latest member
Ka3EeM

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
Top