+ Reply to Thread
Results 1 to 2 of 2

Conditional Concatenation If Cell Contains Data, Concatenate, Else, Move to Next Cell

  1. #1
    Registered User
    Join Date
    09-27-2012
    Location
    Atlanta, GA
    MS-Off Ver
    2010
    Posts
    1

    Conditional Concatenation If Cell Contains Data, Concatenate, Else, Move to Next Cell

    Scenario: 30 Columns and 2000 rows with data. Any given row may have data in as few as two columns or as many as 20 columns. My challenge: Concatentate only the cells with data and insert a line feed between each concatenation within the results.

    1. Skip cells where no data is present
    2. If data is present, Concatenate the column Header, then the data in the cell
    3. Move on to the next cell and repeat steps 1 & 2...WITH ONE EXCEPTION...
    4. Between each instance of concatenation, INSERT a Line Feed AKA: Carriage Return or ALT Enter

    See Attached...much nicer to look at than this crude representation of three rows of representative data and desired results below:

    Color: Material: Handle Type: Size: Length: Width: Height: Weight in Pounds: Guarantee:

    Steel Grip 8"
    Blue Wood Large

    Red 4" 14" 3" 3 1 Year


    Material: Steel
    Handle Type: Grip
    Length: 8"

    Color: Blue
    Material: Wood
    Size: Large

    Color: Red
    Length: 4"
    Width: 14"
    Height: 3"
    Weight in Pounds: 3
    Guarantee: 1 Year
    Attached Files Attached Files

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Conditional Concatenation If Cell Contains Data, Concatenate, Else, Move to Next Cell

    jrdrcs,

    Welcome to the forum!

    Unfortunately, Excel's native CONCATENATE() function doesn't work with arrays. This means that there is no way Excel can perform what you're asking for without the use of VBA or an add-in.

    Attached is a modified version of the workbook you posted.
    I have added the ConcatAll UDF that I created, which is posted here:
    http://www.excelforum.com/tips-and-t...geravatar.html

    Then, in cell K2 is this array formula. Note that array formulas must be entered with Ctrl+Shift+Enter. That's how the formula gets wrapped in the curly braces {}, do not try to add those yourself.
    Please Login or Register  to view this content.
    I enabled the "Wrap Text" option for cell K2, and then copied the cell down.
    Attached Files Attached Files
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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