TEXTJOIN unique names (columns) - ignore blanks

Netrunner

New Member
Joined
Sep 13, 2019
Messages
2
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
aagza,g,z

<tbody>
</tbody>
agz#N/A

<tbody>
</tbody>

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

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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:

Code:
=TEXTJOIN(",",TRUE,IF(A2:D2<>"",A2:D2,""))
 
Last edited:
Upvote 0
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!
 
Upvote 0
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. :eek:
 
Upvote 0
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
aagza,g,z

<tbody>
</tbody>
agz#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,""))
 
Upvote 0
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.
 
Upvote 0
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,""))
 
Upvote 0
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)))
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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