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

Thread: Flexible Concatenation Function

Hybrid View

Previous Post Previous Post   Next Post Next Post
  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
    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)

  3. #3
    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

  4. #4
    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.

  5. #5
    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.

  6. #6
    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.

  7. #7
    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???
    I know I am a few years late with this, but here is a function that with take a delimited text string (which is the output from my ConCat function) and returns the same delimited text with duplicate removed. So, to accomplish what you want, simply pass the output from the ConCat function into the first argument for the Uniques function below and specify the delimiter in the second argument... the output from the Uniques function will be what you asked for.
    Code:
    Function Uniques(Text As String, Delimiter As String) As String
      Dim X As Long, Data() As String
      Data = Split(Text, Delimiter)
      With CreateObject("Scripting.Dictionary")
        For X = 0 To UBound(Data)
          .Item(Data(X)) = 1
        Next
        Uniques = Join(.Keys, Delimiter)
      End With
    End Function

  8. #8
    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

  9. #9
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by snb View Post
    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
    Thank you for posting this solution to the OP... it introduced me to something new as I was not aware of the ArrayList Class before now. I looked it up on line and it seems quite powerful. Thanks again.


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
    https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htJ6TpIOXR
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htOKs4jh3M
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 10-24-2023 at 02:57 PM.

  10. #10
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Last edited by DocAElstein; 09-29-2022 at 07:47 PM.

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
  •