+ Reply to Thread
Results 1 to 5 of 5

TEXTJOIN array for cells with functions in them (bug?)

  1. #1
    Registered User
    Join Date
    04-03-2017
    Location
    Denmark
    MS-Off Ver
    365
    Posts
    92

    TEXTJOIN array for cells with functions in them (bug?)

    Hello,

    I am experiencing great difficulties with the TEXTJOIN function as an array (CSE). In my attached workbook, the "comments" column joins text based on whether or not there are values in columns 1-13.
    It is working fine, however, as soon are there's a function in the cells with numbers, it behaves oddly. It works fine, until i start sorting the cells. Whenever i sort for a value, and then clear the filter, the comment cell, which is supposed to show a string of text, is just blank for the cell where it drags the number from a function.

    In my workbook, the cell H6 drags its number from A16 (234). When i sort, and then clear the filter for a random value, the comment associated with the cell disappears.

    Thanks for your help!
    Attached Files Attached Files
    Last edited by Allerdrengen; 11-01-2018 at 05:24 PM.

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: TEXTJOIN array for cells with functions in them (bug?)

    First try changing the formulas in H and I to
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    as long as there is only one value to reference in A and C this will work.

    Then remove the filters and re apply to only C:P. Sort and it still works at my end.

    Did this help?
    Dave

  3. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: TEXTJOIN array for cells with functions in them (bug?)

    I would cut G3:AA3 and paste at G1:AA1

    Then P3 with CSE
    =TEXTJOIN(", ",TRUE,IF(ISNUMBER(E3:O3)*(E3:O3<>0),(Q$1:AA$1),""))

  4. #4
    Registered User
    Join Date
    04-03-2017
    Location
    Denmark
    MS-Off Ver
    365
    Posts
    92

    Re: TEXTJOIN array for cells with functions in them (bug?)

    Hi, thanks for your reponse.
    I failed to mention that my "real" worksheet is about 50 times bigger with thousands of functions all over. So I would have to redo all of those functions in order for this to work.
    I guess maybe TEXTJOIN isn't the way to do it. Do you have any suggestions for the same result, but with a different function?

  5. #5
    Registered User
    Join Date
    04-03-2017
    Location
    Denmark
    MS-Off Ver
    365
    Posts
    92

    Re: TEXTJOIN array for cells with functions in them (bug?)

    Thanks for your reponse,
    I do however need my headers to be different that the actual comments, hence the comments are placed at the back of the sheet.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How can I modify my array TEXTJOIN formula to output only unique values
    By Victorjo in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 07-06-2018, 01:50 AM
  2. Replies: 2
    Last Post: 11-22-2017, 04:50 AM
  3. (edited title) BUG filtering for blanks (or filtering out all empty rows)
    By zextrot in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-22-2017, 01:51 PM
  4. Filtering out zeros and blanks
    By hbusche in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 07-25-2013, 03:29 PM
  5. Create an array based off values in another array - exclude blanks
    By clifton1230 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-05-2013, 05:35 PM
  6. Filtering with blanks
    By Shadmani in forum Excel General
    Replies: 3
    Last Post: 02-01-2012, 04:36 AM
  7. filtering out blanks..
    By Hassan Alameh in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-21-2005, 09:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1