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.
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

steve the fish

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

Watch MrExcel Video

Forum statistics

Threads
1,099,095
Messages
5,466,619
Members
406,493
Latest member
Hazem Hassan

This Week's Hot Topics

Top