+ Reply to Thread
Results 1 to 3 of 3

Concatenate cells in column range if condition is met

  1. #1
    Registered User
    Join Date
    07-30-2013
    Location
    Osaka, Japan
    MS-Off Ver
    Excel 2003
    Posts
    1

    Concatenate cells in column range if condition is met

    Hi Guys,

    Hope you can help. I'm looking for a VBA macro that can solve my problem.

    I want the macro to go down column C and column D looking for a match with the row below and the row below that etc. and then concatenate column E in to column F. That's the easy bit. The tricky bit is concatenating column E of all the column C and column D matches into the first row of the first match. Hope that makes sense. If there is not a match with the row below concatenate column C and column D into column F.
    Alex V

    A B C D E
    USA 1 scott st Acme Company 1 Service Type 1 Sub service 432
    France 1 scott st Acme Company 1 Service Type 1 Sub service 123
    Italy 2 scott st Acme Company 2 Service Type 2 Sub service 321
    Canada 2 scott st Acme Company 2 Service Type 2 Sub service 453
    Germany 2 scott st Acme Company 2 Service Type 3 Sub service 476
    Bolivia 3 scott st Acme Company 3 Service Type 2 Sub service 766
    Brazil 4 scott st Acme Company 4 Service Type 3 Sub service 333
    Brazil 4 scott st Acme Company 4 Service Type 3 Sub service 367

    Expected outcome:

    A B C D E F
    USA 1 scott st Acme Company 1 Service Type 1 Sub service 432 Sub service 432, Sub service 123
    France 1 scott st Acme Company 1 Service Type 1 Sub service 123
    Italy 2 scott st Acme Company 2 Service Type 2 Sub service 321 Sub service 321, Sub service 453
    Canada 2 scott st Acme Company 2 Service Type 2 Sub service 453
    Germany 2 scott st Acme Company 2 Service Type 3 Sub service 476 Sub service 476
    Bolivia 3 scott st Acme Company 3 Service Type 2 Sub service 766 Sub service 766
    Brazil 4 scott st Acme Company 4 Service Type 3 Sub service 333 Sub service 766, Sub service 333
    Brazil 4 scott st Acme Company 4 Service Type 3 Sub service 367

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Concatenate cells in column range if condition is met

    It’s not possible to get the matching data with concatenated result using a single cell worksheet function. So try the below UDF

    Please Login or Register  to view this content.
    =CLookup(LookupValue,LookupRange,ResultRange)

    For Example
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    How to install your new code
    1. Copy the Excel VBA code to a module
    2. Select the workbook in which you want to store the Excel VBA code
    3. Press Alt+F11 to open the Visual Basic Editor
    4. Choose Insert > Module
    5. Edit > Paste the macro into the module that appeared
    6. Close the VBEditor
    7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Concatenate cells in column range if condition is met

    Thanks for the rep

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. Replies: 12
    Last Post: 12-28-2012, 07:49 PM
  2. Replies: 1
    Last Post: 12-20-2012, 04:18 PM
  3. Concatenate Cells Based on a Condition in Next Row
    By brendanec in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-06-2012, 03:34 PM
  4. Concatenate a Column Range To a Cell
    By PY_ in forum Excel General
    Replies: 2
    Last Post: 07-19-2011, 03:29 PM
  5. concatenate 3 cells based on condition
    By Pasha81 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-02-2009, 09:55 AM

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