Textjoin with If?

[AnyaK]

Hello,

I have several columns which contain numbers. I'd like to use TEXTJOIN with IF so that if, say column J includes the number 1 and column M also contains the number 1, then the column headers will be concatenated/joined (so the result would be "J, M".

I've made various unsuccessful attempts; I just end up getting every column header strung together as opposed to only those with a 1 below.

Here's what I've tried so far:


=TEXTJOIN("; ",TRUE,IF(J2:J10>0,$J$1:$S$1))

=TEXTJOIN(", ",1,INDEX(REPT(J1:S1,J$2:J$10=1),0))

=IF(J2:J10>0,CONCAT(J1:S1))


I know I'm probably missing something glaringly obvious here, but any help would be much appreciated as I'm completely stuck.

Many thanks,

AnyaK


P.S. I regret that I cannot post an image/example at present as my employer doesn't allow this, but I could try to do this later from home if an example might make my problem clearer?

 

[AnyaK]

Hi Aladin,

With the formula being entered in cell H2, I would want the result to be:

Chocolate; Pineapple

H3 – Cassava; Mint
H4 – Bananas; Pineapple
H5 – Cassava
H6 – Chocolate; Pineapple

Many thanks,

AnyaK

 

[Aladin Akyurek]

With TEXTJOIN:

In H2 control+shift+enter, not just enter, and copy down...

=TEXTJOIN("; ",TRUE,IF($B2:$F2=1,$B$1:$F$1,""))

With ACONCAT:

In H2 of Summary control+shift+enter, not just enter, and copy down:

=REPLACE(aconcat(IF($B2:$F2=1,"; "&$B$1:$F$1,"")),1,2,"")

For this formula to work, add the following code for ACONCAT as a Module to your workbook using Alt+F11...

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

 

[AnyaK]

Hi Aladin,

Many thanks for your help, I really appreciate it.

The TEXTJOIN formua works perfectly, thanks so much!

I tried the ACONCAT, but I couldn't get the VBA to run as when I press F5, a "Macro Name" popup appears and is completely blank. Sorry, I don't know VBA at all, yet, so it is just me being stupid.

I can just use the TEXTJOIN formula though, so that's perfect, thanks again.

AnyaK

 

[Aladin Akyurek]

Hi Aladin,

Many thanks for your help, I really appreciate it.

The TEXTJOIN formua works perfectly, thanks so much!

I tried the ACONCAT, but I couldn't get the VBA to run as when I press F5, a "Macro Name" popup appears and is completely blank. Sorry, I don't know VBA at all, yet, so it is just me being stupid.

I can just use the TEXTJOIN formula though, so that's perfect, thanks again.

AnyaK

You are welcome.

To install ACONCAT, according to Rick Rothstein, "simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done."