+ Reply to Thread
Results 1 to 2 of 2

Excel 2007 : combing columns in a cell and separating with a coma

  1. #1
    Registered User
    Join Date
    08-11-2009
    Location
    london england
    MS-Off Ver
    Excel 2007
    Posts
    1

    combing columns in a cell and separating with a coma

    hi

    i am trying to find a solution to this problem.I have looked at various post but can seem to put anything together to get the outcome i need
    PHP Code: 
    A1 model   B1  poduct
    A2 pp2      B2  lop2
    A3 pp2      B3  lop4
    A4 pp3      B4  lop5
    A5 pp3      B5  lop6
    A6 pp3      B6  lop7 
    A1 contains multiple similar model and b1 contains products what assosiate to that model, i want to print out in Coloumn C products which have same model number in Row A .separated by a coma.For example like this

    PHP Code: 
    A1 model   B1 product           C1
    A2 pp2      B2  lop2               C2   lop2
    ,lop4
    A3 pp2      B3  lop4               C3 
    (stays empty as result is copied into C2)
    A4 pp3      B4  lop5               C4 lop5,lop6.lop7
    A5 pp3      B5  lop6               C5 
    (stays empty as result is copied into C4)
    A6 pp3      B6  lop7               C6 (stays empty as result is copied into C4
    The excel file has over 12000 rows and doing it by hand is taking way too long and the list changes daily.

    Any help would be appreciated.

    kind regards

    Nafri

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,491

    Re: combing columns in a cell and separating with a coma

    Hi Nafri, welcome to the forum.

    What you're describing is called String Concatenation. It's not a normal built-in function in Excel, but enough people have written User Defined Functions to add to their worksheets that it is easy to do nowadays. Have you ever worked with a UDF? If not, this will be new but not difficult.

    First, to add code, you're going to have to save your sheet as .xlsm format, remember that. Here's the code we're going to add to your worksheet:
    Please Login or Register  to view this content.
    ========
    How to install the User Defined Function:

    1. Open up your workbook
    2. Get into VB Editor (Press Alt+F11)
    3. Insert a new module (Insert > Module)
    4. Copy and Paste in your code (given above)
    5. Get out of VBA (Press Alt+Q)
    6. Save your sheet

    The function is installed and ready to use.

    =============
    Now, let's say the data looks exactly like your sample, with Models in A2 and down and Product codes in B2 and down.

    In C2, put this formula that uses your new UDF function:
    =CONCATIF($A:$A, A2, $B:$B, ",", TRUE))

    Copy that down. See how it reads a lot like a SUMIF() formula? The added 4th parameter is the delimiter you want to use, and the 5th TRUE/FALSE parameter is where you instruct the function to remove duplicates or not. If TRUE, the lop2 would only appear once if it were found multiple times as a match.

    Now that formula caused a concatenated string to appear in every row, and you said you only wanted a string in the FIRST row of each new model #, so now that you've seen the base UDF in action, here's a longer formula to get the real result you wanted:

    =IF(COUNTIF($A$1:A2, A2) > 1, "",CONCATIF($A:$A, A2, $B:$B, ",", TRUE))

    Attached is a sample book so you can see it.
    Attached Files Attached Files
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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