Results 1 to 6 of 6

Thread: TEXTJOIN unique names (columns) - ignore blanks
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default TEXTJOIN unique names (columns) - ignore blanks

    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
    a g z #N/A

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

  2. #2
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    642
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: TEXTJOIN unique names (columns) - ignore blanks

    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 by kweaver; Sep 13th, 2019 at 08:19 PM.

  3. #3
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,880
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    5 Thread(s)

    Default Re: TEXTJOIN unique names (columns) - ignore blanks

    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!

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,358
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: TEXTJOIN unique names (columns) - ignore blanks

    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.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    New Member
    Join Date
    Sep 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: TEXTJOIN unique names (columns) - ignore blanks

    Thanks. This is what I needed.

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,358
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: TEXTJOIN unique names (columns) - ignore blanks

    Quote Originally Posted by Netrunner View Post
    Thanks. This is what I needed.
    Thanks for letting us know.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •