+ Reply to Thread
Results 1 to 13 of 13

concatenate non-blank values in a matrix array

  1. 10-01-2015, 12:39 PM #1
    gilliamwibson
    gilliamwibson is offline
    Registered User
    Join Date
    10-01-2015
    Location
    DC
    MS-Off Ver
    2013 Pro Plus
    Posts
    22

    Question concatenate non-blank values in a matrix array

    I have a an array formula result as a matrix (retrieved by F9):

    {"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","Triple Jump Afghanistan FMH|Q","Blue Orchard El Salvador FMH|Q","";"","Calvert FI |Q","","","","","","","";"","","","","","","","","";"","","OPIC FMH |Q","","","","","","";"","","","IIC FMH |Q","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"Blue Orchard FI |Q","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","","";"","","","","","","","",""}

    I'd like to have non-"" cells consolidated in one single array (not matrix array) or just concatenated in a single cell. I tried so many non-VBA ways to no avail. please Help! The challenge is to not use VBA.
    Reply With QuoteRegister To Reply
  2. 10-01-2015, 01:35 PM #2
    gilliamwibson
    gilliamwibson is offline
    Registered User
    Join Date
    10-01-2015
    Location
    DC
    MS-Off Ver
    2013 Pro Plus
    Posts
    22

    Exclamation Re: concatenate non-blank values in a matrix array

    I'd like to have non-"" entries consolidated in one single array
    Reply With QuoteRegister To Reply
  3. 10-01-2015, 01:37 PM #3
    Jacc
    Jacc is offline
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,844

    Re: concatenate non-blank values in a matrix array

    The CONCATENATE formula does not accept array input as you may know. Then it is either splitting up that array with so many helper columns and finishing of with so many &
    OR
    use a small UDF.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
    Reply With QuoteRegister To Reply
  4. 10-01-2015, 01:39 PM #4
    gilliamwibson
    gilliamwibson is offline
    Registered User
    Join Date
    10-01-2015
    Location
    DC
    MS-Off Ver
    2013 Pro Plus
    Posts
    22

    Re: concatenate non-blank values in a matrix array

    how small a udf are we talking about sensei?
    Reply With QuoteRegister To Reply
  5. 10-01-2015, 01:47 PM #5
    Jacc
    Jacc is offline
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,844

    Re: concatenate non-blank values in a matrix array

    About this small
    Please Login or Register  to view this content.
    Last edited by Jacc; 10-01-2015 at 04:59 PM.
    Reply With QuoteRegister To Reply
  6. 10-01-2015, 01:53 PM #6
    Jacc
    Jacc is offline
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,844

    Re: concatenate non-blank values in a matrix array

    It's the magic function that Microsoft never gave us, a CONCATENATE function that accepts arrays. Just wrap it around whatever you got and hit Ctrl + Shift + Enter.
    Reply With QuoteRegister To Reply
  7. 10-01-2015, 02:16 PM #7
    gilliamwibson
    gilliamwibson is offline
    Registered User
    Join Date
    10-01-2015
    Location
    DC
    MS-Off Ver
    2013 Pro Plus
    Posts
    22

    Re: concatenate non-blank values in a matrix array

    what does

    ConcatY = "-"

    line do?
    Reply With QuoteRegister To Reply
  8. 10-01-2015, 02:21 PM #8
    gilliamwibson
    gilliamwibson is offline
    Registered User
    Join Date
    10-01-2015
    Location
    DC
    MS-Off Ver
    2013 Pro Plus
    Posts
    22

    Re: concatenate non-blank values in a matrix array

    I just added chr(10) instead of ", " and it gave me exactly what i wanted. thank you Jacc it is very sleek!
    Reply With QuoteRegister To Reply
  9. 10-01-2015, 02:26 PM #9
    Jacc
    Jacc is offline
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,844

    Re: concatenate non-blank values in a matrix array



    The ConcatY = "-" is an error handler kind of thing, if there is nothing to concatenate it will show a dash.
    I use dash in all my IFERROR as well rather than "" because then you know there is a function and not just an empty cell.
    Reply With QuoteRegister To Reply
  10. 10-01-2015, 02:32 PM #10
    gilliamwibson
    gilliamwibson is offline
    Registered User
    Join Date
    10-01-2015
    Location
    DC
    MS-Off Ver
    2013 Pro Plus
    Posts
    22

    Re: concatenate non-blank values in a matrix array

    thank you!!
    Reply With QuoteRegister To Reply
  11. 10-01-2015, 02:33 PM #11
    Jacc
    Jacc is offline
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,844

    Re: concatenate non-blank values in a matrix array

    You are welcome
    Reply With QuoteRegister To Reply
  12. 10-01-2015, 04:59 PM #12
    Jacc
    Jacc is offline
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,844

    Re: concatenate non-blank values in a matrix array

    ----------------
    Last edited by Jacc; 10-01-2015 at 05:06 PM.
    Reply With QuoteRegister To Reply
  13. 10-01-2015, 06:00 PM #13
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: concatenate non-blank values in a matrix array

    How about adapting teh formula that generates that, instead?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to make a symmetric matrix from asymmetric matrix by averaging two values
    By latte in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-05-2014, 03:41 AM
  2. [SOLVED] Array formula: Automatically create a series of values for a one-dimensional matrix
    By bbozze in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-11-2014, 07:48 AM
  3. Concatenate matrix headers
    By menim in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-14-2013, 12:46 PM
  4. Concatenate non-blank values in the same column and add commas
    By chris.huang in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-26-2013, 08:52 PM
  5. If both B4 and D4 are blank write 'this' if either B4 or D4 have values concatenate
    By Greenal in forum Excel General
    Replies: 4
    Last Post: 08-22-2011, 09:42 AM
  6. Howto concatenate Array values
    By rwgrietveld in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-22-2008, 01:28 PM
  7. [SOLVED] How to use an array or matrix to return text vs. numeric values
    By Ingrid in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-09-2005, 08:08 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1