Results 1 to 4 of 4

Thread: Concatenate multiple values

  1. #1
    Junior Member
    Join Date
    Aug 2012
    Posts
    2
    Rep Power
    0

    Lightbulb Concatenate multiple values

    super helpful...I'm attempting to do a modification of this.

    I'd like to concatenate ID numbers from all related rows within the table itself. See the attachment.

    Counting and Summing for all related values is in the table (see column D), but a reporting of the values themselves? Unsure how to do this. Any great ideas? Thanks.

    concat values.xlsx

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg. 9hI1CQJMLLo9hWn2pGBeSS
    https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzkRujoMw9PblmXDQ14AaABAg. 9hJRnEjxQrd9hJoCjomNI2
    https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzPZbG7OvUkh35nXDd4AaABAg. 9hJOZEEZa6p9hJqLC7El-w
    https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwUcEpm8u6ZW3uOHXx4AaABAg. 9hIlxxGY7t49hJsB2PWxC4
    https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyvDj6NWT1Gxyy2JyR4AaABAg. 9hIKlNPeqDn9hJskm92np6
    https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugwy7qx_kG9iUmMVO_F4AaABAg. 9hI2IGUdmTW9hJuyaQawqx
    https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg. 9hI1CQJMLLo9hJwTB9Jlob
    https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyyQWYVP1OnCqavb-x4AaABAg
    https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwJKKmExZ1FdZVDJf54AaABAg
    https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugz_p0kVGrLntPtYzCt4AaABAg
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9iHOYYpaAbC
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgxuL6YCUckeUIh9hoh4AaABAg
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg. 9h4sd6Vs4qE9h7G-bVm8_-
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 09-22-2023 at 05:00 PM.

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Try this UDF.

    Code:
    Function KCONCAT(ByRef ConcatRange, Optional ByVal blnUnique As Boolean = False, _
                                    Optional ByVal Delim As String = ",") As String
    
        ' Function by Krishnakumar @ ExcelFox.com
        
        ' ConcatRange   : Could be either a Range or array
        ' Delim         : By default the delimiter is ","
        ' blnUnique     : If it's TRUE, return only unique values
        ' Limitation    : Works only single row/column or single dimension array
        
        Dim UpperDimension As Long, i As Long, x
        
        If TypeOf ConcatRange Is Range Then
            
            If (ConcatRange.Rows.Count > 1) * (ConcatRange.Columns.Count = 1) Then
                KCONCAT = Join$(Application.Transpose(ConcatRange.Value), Delim)
            ElseIf (ConcatRange.Rows.Count = 1) * (ConcatRange.Columns.Count > 1) Then
                KCONCAT = Join$(Application.Transpose(Application.Transpose(ConcatRange.Value)), Delim)
            ElseIf (ConcatRange.Rows.Count > 1) * (ConcatRange.Columns.Count > 1) Then
                KCONCAT = CVErr(xlErrNA)
            End If
        
        ElseIf IsArray(ConcatRange) Then
            
            On Error Resume Next
            UpperDimension = UBound(ConcatRange, 2)
            On Error GoTo 0
            
            If UpperDimension = 0 Then
                KCONCAT = Join$(Application.Transpose(Application.Transpose(ConcatRange)), Delim)
            Else
                KCONCAT = Join$(Application.Transpose(ConcatRange), Delim)
            End If
            
            KCONCAT = Replace(Replace(KCONCAT, Delim & "False", ""), "False" & Delim, "")
        
        End If
        If blnUnique Then
            With CreateObject("scripting.dictionary")
                .comparemode = 1
                x = Split(KCONCAT, Delim)
                For i = 0 To UBound(x)
                    .Item(x(i)) = Empty
                Next
                If .Count Then KCONCAT = Join$(.keys, Delim)
            End With
        End If
        
    End Function
    and try

    in E2 and copied down,

    =KCONCAT(IF($B$2:$B$11=B2,$A$2:$A$11),,", ")

    It's an array formula. Conformed with CTRL + SHIFT + ENTER


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 07-07-2023 at 01:10 PM.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by mcpizzle View Post
    super helpful...I'm attempting to do a modification of this.

    I'd like to concatenate ID numbers from all related rows within the table itself. See the attachment.

    Counting and Summing for all related values is in the table (see column D), but a reporting of the values themselves? Unsure how to do this. Any great ideas? Thanks.
    You can use the UDF (user defined function) that I posted here to do that...

    LookUp Value and Concatenate All Found Results

    HOW TO INSTALL UDFs
    ------------------------------------
    If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the code from the above linked article into the code window that just opened up. That's it.... you are done. You can now use LookUpConcat just like it was a built-in Excel function. For the question you asked, put this formula in E2 and copy it down to E11...

    =LookUpConcat(B2,B$2:B$11,A$2:A$11,", ")

    There are a couple of other optional parameters available for this function which do not appear to be needed for the question you asked, but you should read the above linked article to see what they are in case things change for your situation.
    Last edited by Rick Rothstein; 08-26-2012 at 08:47 PM.

  4. #4
    Junior Member
    Join Date
    Aug 2012
    Posts
    2
    Rep Power
    0
    Quote Originally Posted by Rick Rothstein View Post
    You can use the UDF (user defined function) that I posted here to do that...

    LookUp Value and Concatenate All Found Results
    Brilliant. My first UDF. I grokked the code of Rick's a bit more than Admin's, so I started there, and it worked like a charm.

Similar Threads

  1. Replies: 7
    Last Post: 05-15-2013, 02:56 PM
  2. Loop to two columns and Concatenate values
    By ivandgreat in forum Excel Help
    Replies: 15
    Last Post: 04-14-2013, 08:20 PM
  3. Function to Concatenate Values
    By Admin in forum Download Center
    Replies: 1
    Last Post: 12-13-2012, 11:38 AM
  4. Concatenate Multiple Lookup Values in Single Cell
    By Admin in forum Download Center
    Replies: 4
    Last Post: 04-06-2012, 09:07 PM
  5. Pass Values Between Multiple Userforms
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 07-24-2011, 03:25 AM

Posting Permissions

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