TEXTJOIN with Multiple Conditions

[Jer2224]

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.

 

[steve the fish]

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]

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]

After you put that CSE formula (CSE mean Control+Shift+Enter)

press the keys Control+Shift+Enter simultaneously

 

[Jer2224]

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]

So do you mean the cell contains review rather than is review?

 

[Jer2224]

So do you mean the cell contains review rather than is review?

Yes exactly. Sorry for not making that clear initially.....

 

[steve the fish]

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]

Thanks - I will have a play around with this and report back. Many thanks for your assistance so far! Cheers. Jer2224.

 

[Jer2224]

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.