Flexible Concatenation Function

[nlk.public]

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

[Rick Rothstein]

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.

[nlk.public]

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

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.

[Rick Rothstein]

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.

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