TEXTJOIN unique names (columns) - ignore blanks

[Netrunner]

I am trying to concatenate unique column text into a single cell. After spending an afternoon researching, I can now concatenate all the text in different column cells and remove duplicates. However, if one of these cells is empty, I get a blank value.

as an example, I want to concatenate all the letters in one cell using this formula: =TEXTJOIN(",",TRUE,IF(COLUMN(A2:D2)=MATCH(A2:D2,A2:D2,0),A2:D2,""))
A B C D E
[TABLE="width: 320"]
<tbody>[TR]
[TD="width: 64"]a[/TD]
[TD="width: 64"]a[/TD]
[TD="width: 64"]g[/TD]
[TD="width: 64"]z[/TD]
[TD="class: xl63, width: 64"]a,g,z[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 320"]
<tbody>[TR]
[TD="width: 64"]a[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]g[/TD]
[TD="width: 64"]z[/TD]
[TD="class: xl65, width: 64"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

What do I need to add such that it ignores blank cells?

 

[kweaver]

Try this array formula (CTRL+Shift+Enter), but it doesn't remove the duplicates -- maybe incorporate that into yours? I'm still scratching my head:

=TEXTJOIN(",",TRUE,IF(A2:D2<>"",A2:D2,""))

 

[Domenic]

Try...

=TEXTJOIN(",",TRUE,IF(A2:D2<>"",IF(MATCH(A2:D2,A2:D2,0)=COLUMN(A2:D2)-COLUMN(A2)+1,A2:D2,""),""))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Peter_SSs]

Welcome to the MrExcel board!

As a matter of interest, you *could* have dealt with the blanks with this adjustment to your formula ..

{=TEXTJOIN(",",TRUE,IF(COLUMN(A2:D2)=MATCH(A2:D2&"#",A2:D2&"#",0),A2:D2&"",""))}

.. but I would still recommend using Domenic's formula as your original formula, and this one I have posted, both suffer from the problem that if new columns are subsequently added at the left of the worksheet they will then return incorrect results.

 

[Netrunner]

Thanks. This is what I needed.

 

[Peter_SSs]

Thanks. This is what I needed.

Thanks for letting us know.

 

[ajswanso]

I am trying to concatenate unique column text into a single cell. After spending an afternoon researching, I can now concatenate all the text in different column cells and remove duplicates. However, if one of these cells is empty, I get a blank value.

as an example, I want to concatenate all the letters in one cell using this formula: =TEXTJOIN(",",TRUE,IF(COLUMN(A2:D2)=MATCH(A2:D2,A2:D2,0),A2:D2,""))
A B C D E

a

a

g

z

a,g,z

<tbody>
</tbody>

a

g

z

#N/A

<tbody>
</tbody>

What do I need to add such that it ignores blank cells?

I got this version to work as well.

=TEXTJOIN(",",TRUE,IF(IFERROR(COLUMN(A1:D1)=MATCH(A1:D1,A1:D1,0),0),A1:D1,""))

 

[Peter_SSs]

I got this version to work as well.

Welcome to the MrExcel board!

Although that works, it suffers from the same potential problem as mentioned previously:

your original formula, and this one I have posted, both suffer from the problem that if new columns are subsequently added at the left of the worksheet they will then return incorrect results.

Try adding a new column A after you have entered the original data and your formula.

 

[ajswanso]

This is true. A simple revision can ensure any columns added before do not prevent this from working.
=TEXTJOIN(",",TRUE,IF(IFERROR(COLUMN(B1:E1)=(MATCH(B1:E1,B1:E1,0)+COLUMN(B1)-1),0),B1:E1,""))
or a revised version of the original location
=TEXTJOIN(",",TRUE,IF(IFERROR(COLUMN(A1:D1)=(MATCH(A1:D1,A1:D1,0)+COLUMN(A1)-1),0),A1:D1,""))

 

[Peter_SSs]

or a revised version of the original location
=TEXTJOIN(",",TRUE,IF(IFERROR(COLUMN(A1:D1)=(MATCH(A1:D1,A1:D1,0)+COLUMN(A1)-1),0),A1:D1,""))

Quite so, although now it is very similar to post #3 suggestion (with a slightly different approach to deal with blanks)

Another slight variation could be
=TEXTJOIN(",",TRUE,IF(COLUMN(A2:D2)=(IFNA(MATCH(A2:D2,A2:D2,0),0)+COLUMN(A2)-1),A2:D2,""))

Or for a different approach altogether (assuming appropriate Excel version)
=LET(r,A2:D2,TEXTJOIN(",",,UNIQUE(FILTER(r,r<>"",""),1)))