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?

 

[Eric W]

What row is the 1 in? Or do you want to check multiple rows, i.e., if there's a 1 in J2:J10, then include J1, if there's a 1 in K2:K10, then include K1, etc.?

 

[AnyaK]

Hi Eric,

Thanks for your reply.

I'd like to check multiple rows (as you described perfectly in the second part of your question to me).

Many thanks,

AnyaK

 

[Eric W]

You're not missing anything glaringly obvious, it's actually pretty tricky. Try this:

=TEXTJOIN("; ",TRUE,IF(MMULT(TRANSPOSE(ROW(J2:J10)),--(J2:S10=1)),J1:S1,""))

confirmed with Control+Shift+Enter. Change the ranges to match your sheet, particularly the bottom row.

 

[AnyaK]

Hi Eric,

Many thanks, I really appreciate your help and time.

I regret that with the suggested formula, I'm still getting the same issue where all of the column headers result, rather than just the headers of those columns containing 1.

I'm not sure what else I could try? Or perhaps VBA might be needed? (I only know terribly basic VBA, so wouldn't be quite sure where to start, though.)

Thanks again,

AnyaK

 

[Aladin Akyurek]

Care to post a 5 row/5 column sample (input) along with expected output?

 

[AnyaK]

Hi Aladin,

Many thanks for your reply.

My employer blocks this ability at work for security reasons, but I'll put together a sample and post it this evening, from home.

Thanks,

AnyaK

 

[Eric W]

Here's the layout I used for testing:

	H	I	J	K	L	M	N	O	P	Q	R	S
1	Headers		A	B	C	D	E	F	G	H	I	J
2	A; C; E; F; I					y
3			1		1	n					1
4				2		y	1					One
5						n
6					1	y
7						n
8
9
10								1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula H2 {=TEXTJOIN("; ",TRUE,IF(MMULT(TRANSPOSE(ROW(J2:J10)),--(J2:S10=1)),J1:S1,""))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Perhaps you could try it, or explain how it differs from your sheet?

Yes, we could write something up in VBA easy enough, but the above formula should work, if you have the TEXTJOIN function. Until we understand why it doesn't, writing up the UDF probably wouldn't work any better. What version of Excel do you have, what OS?

 

[AnyaK]

Hi,

So sorry for the delay. I struggled to create an image (I'm autistic with mild learning difficulties) and got muddled up and stressed.

The image is a 'made up' version as the original that I'm actually working on contains personal data.

Excel 2013 64 bit

	A	B	C	D	E	F	G
1	Person's Unique ID	Chocolate	Bananas	Pineapple	Cassava	Mint	Street
2	123	1	0	1	0	0	Lavendar Road
3	456	0	0	0	1	1	Rose Street
4	789	0	1	1	0	0	Tulip Way
5	234	0	0	0	1	0	Chrysanthemum Road
6	567	1	0	1	0	0	Daisy Close

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

I've tried using:

=TEXTJOIN("; ",TRUE,IF(MMULT(TRANSPOSE(ROW(B2:B6)),--(B2:F6=1)),B1:F1,""))

But I've obviously screwed up as I just get #NAME ? error in the above pasted example. I am pressing Ctrl + Shift + Enter, but I'm still obviously going wrong somewhere.

I'm really sorry, I know I must be very annoying.

I'd really appreciate your help (and thanks for being patient with me).

At work, I use Windows 10, Excel 2016

At home it's Windows 8, Excel 2013 (which it now occurs to me is why TEXTJOIN formula doesn't work at home for me...)

AnyaK

 

[Aladin Akyurek]

Care also to post the output which must obtain from the sample of post #9 ?