Textjoin with If?

A

AnyaK

New Member
Joined
Jun 5, 2017
Messages
36
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
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.?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Here's the layout I used for testing:

HIJKLMNOPQRS
HeadersABCDEFGHIJ
A; C; E; F; Iy
n
yOne
n
y
n

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]{=TEXTJOIN("; ",TRUE,IF(MMULT(TRANSPOSE(ROW(J2:J10)),--(J2:S10=1)),J1:S1,""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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?
 
Upvote 0
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
ABCDEFG
Person's Unique IDChocolateBananasPineappleCassavaMintStreet
Lavendar Road
Rose Street
Tulip Way
Chrysanthemum Road
Daisy Close

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]789[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]567[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</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
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Filter and Textjoin formula if two columns match a text value in a cell - excel 365 in a Mac
Replies
7
Views
367
Fluff
Fluff
D
  • Question Question
Xlookup or similar
Replies
6
Views
183
Peter_SSs
Peter_SSs
B
  • Question Question
Suggestions for Building a Historical Cost Database by Project
Replies
0
Views
98
babyjwhale
B
B
  • Question Question
Matching Multiple Header Row Cell Values - Copy the data below to next available row.
Replies
2
Views
572
B-radK
B
S
  • Question Question
The number 0 is not displayed in the cell
Replies
3
Views
266
myall_blues
myall_blues

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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
Back
Top