Page 3 of 3 FirstFirst 123
Results 21 to 24 of 24

Thread: Flexible Concatenation Function

  1. #21
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    or ?

    Code:
    Sub ConcatDashedRanges()
      Columns(1).replace "=","'="
    
      For Each Ar In Columns(1).SpecialCells(2).Areas
        Ar(1).Offset(, 1) = Application.Trim(Join(filter(filter(Application.Transpose(Ar.Value),"--",false),"'=",false), ";"))
      Next
    End Sub

  2. #22
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by snb View Post
    or ?

    Code:
    Sub ConcatDashedRanges()
      Columns(1).replace "=","'="
    
      For Each Ar In Columns(1).SpecialCells(2).Areas
        Ar(1).Offset(, 1) = Application.Trim(Join(filter(filter(Application.Transpose(Ar.Value),"--",false),"'=",false), ";"))
      Next
    End Sub
    Your code does not appear to include the cells that started with the equal sign within the concatenated output from the macro (at least on my tests it doesnt').

  3. #23
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    That's correct; they have been filtered out.
    You can remove the filter that does that: filter(...,"'=","")

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

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
  •