Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: Flexible Concatenation Function

  1. #1
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    See this link FORMATTED Flexible Concatenation Function for this article's companion function.

    Unfortunately, Excel's CONCATENATE function does not work with ranges or arrays (kind of making it worthless in my opinion since simply concatenating the text together using an ampersand produces the same result more compactly). However, the following UDF (user defined function), which can also be used as a function called from other VB code if desired, which I have posted in the past, does work with ranges...

    Code:
    Function ConCat(Delimiter As Variant, ParamArray CellRanges() As Variant) As String
      Dim Index As Long, Rw As Long, Col As Long, Down As Boolean, Rng As Range, Cell As Range
      If IsMissing(Delimiter) Then Delimiter = ""
      Index = LBound(CellRanges)
      Do While Index <= UBound(CellRanges)
        If TypeName(CellRanges(Index)) = "Range" Then
          Set Rng = CellRanges(Index)
          If Index < UBound(CellRanges) Then
            If TypeName(CellRanges(Index + 1)) <> "Range" Then Down = CellRanges(Index + 1) = "|"
          End If
          If Down Then
            For Col = 0 To Rng.Columns.Count - 1
              For Rw = 0 To Rng.Rows.Count - 1
                If Len(Rng(1).Offset(Rw, Col).Value) Then
                  ConCat = ConCat & Delimiter & Rng(1).Offset(Rw, Col)
                End If
              Next
            Next
            Index = Index + 1
          Else
            For Each Cell In Intersect(Rng, Rng.Parent.UsedRange)
              If Len(Cell.Value) Then ConCat = ConCat & Delimiter & Cell.Value
            Next
          End If
        Else
          If CellRanges(Index) = "||" Then
            ConCat = ConCat & Delimiter & "|"
          Else
            ConCat = ConCat & Delimiter & CellRanges(Index)
          End If
        End If
        Index = Index + 1
      Loop
      ConCat = Mid(ConCat, Len(Delimiter) + 1)
    End Function
    This function allows you to create formulas like this...

    =ConCat("-",A1:B3,C1,"HELLO",E1:E2)

    and the contents of the indicated cells, and the text constant "HELLO", will be concantenated together, in the order shown, with a dash between them. For rectangular ranges, the default direction of concatenation is across the columns of a row before advancing to the next row. If you follow the rectangular range with a pipe-symbol (|) in quotes, then the direction of processing changes to down the rows of a column before advancing to the next column. Consider the following portion of a worksheet and note the differing order of the number words in each rectangular range.

    B C D E F
    1 .
    2 one three five
    3 two four six
    4
    5 seven eight nine
    6 ten eleven twelve
    7 .

    The formula...

    =ConCat(", ",C2:E3,"|",C5:E6)

    will produce this output...

    one, two, three, four, five, six, seven, ten, eight, eleven, nine, twelve

    Then "|" argument is not concatenated into the outputted text because it only serves as a signal to the function to concatenate down the columns before advancing across the columns. If the preceding argument was not a range, then the pipe symbol would have been outputted normally. So, you might be wondering, how, in the unlikely event you needed to, would you output a pipe symbol that follows a range without having it be interpretted as a signal to change the direction of concatenation? Simply double it up like this...

    =ConCat(", ",C2:E3,"||",C5:E6)

    If you needed to change the direction of concatenation and output a pipe symbol as well, just use two pipe symbols as the argument instead, like this...

    =ConCat(", ",C2:E3,"|","|",C5:E6)

    The first pipe symbol will be used as the direction of concatenation indicator and the second one as a simple text character. Note, the pipe symbol immediately following any range (even a single column or single row one) will be interpretted as an indicator to change the direction of concatenation. For single row or column ranges, following them with a pipe symbol or not following them with one, the output will be the same.

    The delimiter (first argument) is required, but if you want to concatenate cells, cell ranges or text together without a delimiter, you can specify the empty string ("") as the first argument or simply omit it (but still use the comma as a placeholder in order to delineate the argument position). So, you could concatenate my above example cells and text, but with no delimiter between them, either like this...

    =ConCat("",A1:B3,C1,"HELLO",E1:E2)

    or like this (note the leading comma)...

    =ConCat(,A1:B3,C1,"HELLO",E1:E2)

    your choice.

    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 above code into the code window that just opened up. That's it.... you are done. You can now use ConCat just like it was a built-in Excel function (as shown in the examples above).
    Last edited by Rick Rothstein; 02-12-2013 at 01:29 AM. Reason: Added new functionality to the function

  2. #2
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Hi Rick,

    Nice function you wrote.
    Using the ADO 3.0 library I came up with:

    Code:
    Function concat_snb(c00, c01, c02, c03)
        With New ADODB.Recordset
          .Open "SELECT [" & c00 & "] FROM `" & c01 & "$`;", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ActiveWorkbook.FullName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
          concat_snb = .GetString(, , c02, c03)
        End With
    End Function
    
    Sub snb()
        MsgBox concat_snb(Range("D1").Value, ActiveSheet.Name, "|", vbLf)
    End Sub
    In this case you can choose:
    - which field delimiter to use
    - which row delimiter to use
    - which fields to concatenate
    - how many rows to concatenate



    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h78GftO_iE
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h77HSGDH4A
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h76fafzcEJ
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h759YIjlaG
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h74pjGcbEq
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgzJJUDVv2Mb6YGkPYh4AaABAg. 9h5uPRbWIZl9h7165DZdjg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 07-10-2023 at 06:55 PM.

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

    Welcome to ExcelFox !!

    MS has documented that there is a memory leakage while you querying data from an open Excel.

    BUG: Memory leak occurs when you query an open Excel worksheet by using ActiveX Data Objects (ADO)
    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)

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by snb View Post
    Nice function you wrote.
    Using the ADO 3.0 library I came up with:
    Thanks for commenting. I have never had to deal with data base programming before, so I cannot comment on your code. However, I do notice that Admin has responded indicating that a problem may exist with your approach, so I'll just let things stand at that.

  5. #5
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    I have revised the code for the ConCat function in Message #1 (the original thread message) and am using this message as an alert to those who may be subscribed to this thread. I have added the ability specify which order cells in rectangular ranges should be concatenated in... read the revised description in Message #1 to see how to work with this new functionality.

  6. #6
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    concatenation row by row:

    Code:
    Function ConCat_snb(delimiter As String, ParamArray CellRanges() As Variant) As String
      For Each it In CellRanges
        If VarType(it) = 8204 Then
          For j = 1 To UBound(it.Value)
            concat_snb = concat_snb & delimiter & Join(Application.Index(it.Value, j), delimiter)
          Next
        Else
          concat_snb = concat_snb & delimiter & it
        End If
      Next
    
      ConCat_snb = Mid(concat_snb, 2)
    End Function
    concatenation column by column:

    Code:
    Function ConCat_snb(delimiter As String, ParamArray CellRanges() As Variant) As String
      For Each it In CellRanges
        If VarType(it) = 8204 Then
          For j = 1 To UBound(it.Value, 2)
            concat_snb = concat_snb & delimiter & Join(Application.Transpose(Application.Index(it.Value, , j)), delimiter)
          Next
        Else
          concat_snb = concat_snb & delimiter & it
        End If
      Next
    
      ConCat_snb = Mid(concat_snb, 2)
    End Function

  7. #7
    Junior Member
    Join Date
    Jan 2013
    Posts
    2
    Rep Power
    0

    Edit to make it remove duplicates?

    Rick, brilliant UDF. Could this be modified to exclude duplicate values? Let's say the range A1,B1,C1,D1 has values of ABC,DEF,ABC,123. The desired result of the modified concat UDF would be to display in one cell: ABC,DEF,123 (cutting out the second ABC).

    Doable???

    -N
    Last edited by Admin; 02-02-2013 at 09:11 AM.

  8. #8
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by nlk.public View Post
    Rick, brilliant UDF. Could this be modified to exclude duplicate values? Let's say the range A1,B1,C1,D1 has values of ABC,DEF,ABC,123. The desired result of the modified concat UDF would be to display in one cell: ABC,DEF,123 (cutting out the second ABC).

    Doable???
    Thank you for your nice comment about my UDF... much appreciated. As for you question... I am not sure, there is a lot going on in the code and I am not sure how easy it would be to modify it for your request. But I must ask... do you really think it is necessary to add a "Uniques Only" feature? I mean, the user is constructing the list that is to be concatenated... why would they duplicate a cell or set of cells if they don't want it in the output in the first place? It would seem the user would automatically filter his/her list to avoid duplicates... after all, the reason they are using the function in the first place is to craft the output string to look a certain way via the list they are inputting... I have trouble imagining a situation where they would duplicate cells they did not want duplicated and, if they did so by accident, it would seem easy enough for them to go back and modify the input list to remove the cells they did not actually want in their output text.

  9. #9
    Junior Member
    Join Date
    Jan 2013
    Posts
    2
    Rep Power
    0

    Indeed necessary

    Your questions are fair and I can say, at least for my application the ability to remove duplicates is 100% necessary.

    The situation is such that we need to return, in a single cell, all the possible values that appear in column X. It's less the creation of a specific series of numbers, rather a listing of all the unique values. I assure you that all the values (duplicate or not) in column X need to exist.

    If it is not feasible to build that functionality into the UDF, I accept defeat as an option


    Quote Originally Posted by Rick Rothstein View Post
    Thank you for your nice comment about my UDF... much appreciated. As for you question... I am not sure, there is a lot going on in the code and I am not sure how easy it would be to modify it for your request. But I must ask... do you really think it is necessary to add a "Uniques Only" feature? I mean, the user is constructing the list that is to be concatenated... why would they duplicate a cell or set of cells if they don't want it in the output in the first place? It would seem the user would automatically filter his/her list to avoid duplicates... after all, the reason they are using the function in the first place is to craft the output string to look a certain way via the list they are inputting... I have trouble imagining a situation where they would duplicate cells they did not want duplicated and, if they did so by accident, it would seem easy enough for them to go back and modify the input list to remove the cells they did not actually want in their output text.

  10. #10
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    E.g. in cell K1: "=unique_sorted_concat_snb(A1:F10,",")

    Code:
    Function unique_sorted_concat_snb(c00, c01)
        With CreateObject("System.Collections.ArrayList")
            For Each cl In c00.SpecialCells(2)
              If Not .contains(cl.Value) Then .Add cl.Value
            Next
            .Sort
            
            unique_sorted_concat_snb = Join(.toarray(), c01)
        End With
    End Function

Similar Threads

  1. UDF (user defined function) replacement for Excel's DATEDIF function
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 21
    Last Post: 03-07-2015, 09:47 PM
  2. FORMATTED Flexible Concatenation Function
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 1
    Last Post: 10-14-2012, 03:48 PM

Tags for this Thread

Posting Permissions

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