+ Reply to Thread
Results 1 to 6 of 6

Combine Multiple Rows into single cell

  1. #1
    Registered User
    Join Date
    01-27-2016
    Location
    Wisconsin
    MS-Off Ver
    2016
    Posts
    11

    Combine Multiple Rows into single cell

    Hi -
    I am trying to accomplish a data formatting task using a formula or function and have had no luck.

    The input data looks like this:
    Part Number Vehicle Fitment(s)
    1234 99-00 Ford F-150
    4567 99-00 Chevrolet Silverado 1500
    99-00 Ford F-350
    8910 11-16 Toyota Camry
    12-14 Honda Civic
    11-17 Chevy Cobalt
    09-13 Subaru Forester
    10-12 Chevy Malibu
    13-15 Dodge Challenger

    I would like the data that is currently in multiple cells (regardless of the number of fitments) consolidate into a single cell - separated by carriage returns. This sounds strange, but this is mean to be printed in a book for one time use.

    The desired output of the data looks like this:

    Part Number Vehicle Fitment(s)
    1234 99-00 Ford F-150
    4567 99-00 Chevrolet Silverado 1500
    99-00 Ford F-350
    8910 11-16 Toyota Camry
    12-14 Honda Civic
    11-17 Chevy Cobalt
    09-13 Subaru Forester
    10-12 Chevy Malibu
    13-15 Dodge Challenger

    I am dealing with 5000+ records with different values so I am trying to avoid a manual effort.

    Any assistance or guidance is appreciated!

    Thank you!
    Jesse
    Last edited by 1brandeja5; 01-27-2016 at 07:29 PM. Reason: solved

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Combine Multiple Rows into single cell

    Okay, if your data is in columns A and B starting in row 2, select cell C2 and go to "Define Name" (On "Formulas" tab of ribbon),
    NAME: ThisData
    Refers to: =Sheet1!B2:INDEX(Sheet1!$B2:$B11, MATCH(TRUE, ISNUMBER(Sheet1!$A3:$A11),0))
    Enter

    Then copy this VBA into your spreadsheet (ALT + F11 to open VBA Editor)
    Please Login or Register  to view this content.
    Close the VBA Editor
    That's a function by TigerAvatar that concatenates all within a specific range (which will be "ThisData"

    In C2 copied down

    =IF(A2,CONCATALL(ThisData,","),""&CHAR(10))
    See attachment
    Oh, almost forgot, at end of data in A11, I entered a 0 so formula had a stopping point. Note my range in the defined name included the 0.
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

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

  3. #3
    Registered User
    Join Date
    01-27-2016
    Location
    Wisconsin
    MS-Off Ver
    2016
    Posts
    11

    Re: Combine Multiple Rows into single cell

    This is fantastic. Thank you so much for your thoughtful response and sample sheet. This worked great. I did adjust the 'ThisData' range to look out 30 cells instead of 10 due to the number of applications certain parts have.

    thanks again ChemistB!!
    Jesse

  4. #4
    Registered User
    Join Date
    01-27-2016
    Location
    Wisconsin
    MS-Off Ver
    2016
    Posts
    11

    Re: Combine Multiple Rows into single cell

    Hi ChemistB - can you help me adapt this to item numbers that are alphanumeric? I am running into issues when our part numbers have letters. Some examples are 1234-2, SW1234, etc. I appreciate your assistance.

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Combine Multiple Rows into single cell

    Change "ThisData" to
    =Sheet1!B2:INDEX(Sheet1!$B2:$B$11, MATCH(TRUE,( LEN(Sheet1!$A3:$A$11)>0),0))

    In C2 copied down
    =IF(LEN(A2)>0,CONCATALL(ThisData,CHAR(10)),"")
    Does that work?
    Last edited by ChemistB; 01-29-2016 at 01:10 PM.

  6. #6
    Registered User
    Join Date
    01-27-2016
    Location
    Wisconsin
    MS-Off Ver
    2016
    Posts
    11

    Re: Combine Multiple Rows into single cell

    I couldnt get that to work in it's format. Since the number of repetitions of applications can vary up to 50 applications for a single part, I increased the distance to check for associations so my values are:

    ThisData:
    =Sheet1!B2:INDEX(Sheet1!$B2:$B51, MATCH(TRUE,( LEN(Sheet1!$A2:$A51)>0),0))

    In C2 copied down:
    =IF(LEN(A2)>0,CONCATALL(ThisData,CHAR(10)),"")

    It is only grabbing the first value for some reason. Am I missing something?

    Thanks,
    Jesse

+ 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. Combine Data in Multiple Rows into Single Row
    By koonwei605 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-18-2014, 07:43 AM
  2. [SOLVED] Combine multiple ALMOST duplicate rows into a single row
    By Styler001 in forum Excel General
    Replies: 3
    Last Post: 07-31-2014, 02:30 AM
  3. [SOLVED] Formula to combine multiple formulas into a single cell
    By pdx-adm-trm01 in forum Excel General
    Replies: 7
    Last Post: 03-12-2014, 02:26 PM
  4. Replies: 6
    Last Post: 02-19-2014, 10:25 AM
  5. formula to combine multiple cells into single cell where reference is same
    By rexer231 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-12-2014, 11:44 AM
  6. [SOLVED] How do I combine multiple lines of information into a single cell, separated by commas?
    By ilostar in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-22-2012, 11:48 AM
  7. Combine multiple rows into a single row with concatenation
    By MichaelWood in forum Excel General
    Replies: 7
    Last Post: 06-30-2012, 01:49 PM

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