Page 1 of 2 1 2 LastLast
Results 1 to 10 of 16

Thread: Concatenate multiple results into single cells based on criteria check

  1. #1

    Concatenate multiple results into single cells based on criteria check



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have two columns in Workbook1 - "Issues" and "Regions":
    Issues Regions
    4455 Atlantic
    2555 Central
    7777 Midwestern
    3333 Central
    4444 Central
    6333 Midwestern


    I like to list all Issues according to Regions with their corresponding Issues in second Workbook or Excel File such as below:

    Regions Issues
    Atlantic 4455
    Central 2555, 3333, and 4444
    Midwestern 7777 and 6333


    Could you show me a formula to do this and how? Thanks.

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,436
    Articles
    0
    Excel Version
    Excel 2016
    You will need to use a user-defined function to achieve this if you want the values in single cells....

    Add this UDF in a new VBA Module (ALT+F11 >> Insert >> Module)

    Code:
    Function aconcat(a As Variant, Optional sep As String = "") As String
    ' Harlan Grove, Mar 2002
    Dim y As Variant
    
    If TypeOf a Is Range Then
    For Each y In a.Cells
    aconcat = aconcat & y.Value & sep
    Next y
    ElseIf IsArray(a) Then
    For Each y In a
    aconcat = aconcat & y & sep
    Next y
    Else
    aconcat = aconcat & a & sep
    End If
    
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
    End Function
    Then assuming your data is in Sheet1, A1:B10, and your lookup values are in Sheet2, starting in A2, then in B2 enter formula:

    =SUBSTITUTE(TRIM(aconcat(IF(Sheet1!$B$2:$B$10=A2,Sheet1!$A$2:$A$10,"")," "))," ",",")

    confirmed with CTRL+SHIFT+ENTER and not just ENTER, then copy down.


  3. #3

  4. #4
    My first excel file is in this path "C:\Desktop\Issue Report 1.xls". How do I put that in the SUBSTITUTE? Thanks.

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,436
    Articles
    0
    Excel Version
    Excel 2016
    the "y" is just a variable used in the UDF to hold the cell values in.

    try formula:

    =SUBSTITUTE(TRIM(aconcat(IF('C:\Desktop\[time example.xlsx]Sheet1'!$B$2:$B$10=A2,'C:\Desktop\[time example.xlsx]Sheet1'!$A$2:$A$10,"")," "))," ",",")

    assuming your sheetname at that path is Sheet1.


  6. #6
    Okay, I tried this:

    Sheet 1 is in my first excel file worksheet, Region is my second excel file worksheet.

    =SUBSTITUTE(TRIM(aconcat(IF([Sheet]Sheet1!$B$2:$B$10=A2,Region!$A$2:$A$10,"")," "))," ",",")

    I'm rather getting "Atlantic, Central,Midwest" concatenated together. I'm actually looking to concatenate all Issues by regions rather. e.g. Central "2222, 3333". How do I correct the formula? Thanks.
    Last edited by Excel Learner 77; 2014-09-02 at 04:37 PM.

  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,436
    Articles
    0
    Excel Version
    Excel 2016
    You also need to replace the Region! part with [Sheet]Sheet1!

    =SUBSTITUTE(TRIM(aconcat(IF([Sheet]Sheet1!$B$2:$B$10=A2,[Sheet]Sheet1!$A$2:$A$10,"")," "))," ",",")


  8. #8
    Woww...yes, the issues numbers now show up..however, they're all showing up and point to one region...e.g. Atlantic 1111, 2222, 3333, 4444, 5555, and 6666.

    So in my second excel sheet i prepared column A, formula is in b2:

    Atlantic 1111, 2222, 3333, 4444, 5555, 6666
    central
    Midwest


    How can I just filter out by regions with their corresponding Issue Numbers?

  9. #9
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,436
    Articles
    0
    Excel Version
    Excel 2016
    Did you confirm with CTRL+SHIFT+ENTER?


  10. #10
    I might not have..press all three keys together? And do I re-enter the formula?

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •