Removing duplicates from a string accounting for commas

[bioplz]

I am using a userform to that is populated from an excel sheet and modified from another userform. I used this code to remove duplicates, but it doesn't account for commas.

Function Uniques(Text As String) As String
  Dim X As Long, Data() As String
  Data = Split(Text)
  With CreateObject("Scripting.Dictionary")
    [COLOR=#0000ff].CompareMode=1[/COLOR]
    For X = 0 To UBound(Data)
      .Item(Data(X)) = 1
    Next
    Uniques = Join(.keys)
  End With
End Function

Is there any easy way to account for commas?

For example: 10.1, 10.2, 10.3, 10.4, 10.1, 10.2, 10.3
output with this code is: 10.1, 10.2, 10.3, 10.4, 10.3 because there is no comma after the second 10.3 it does not register as a duplicate.

I want the output to remove the last number and the commas. So, essentially, I want the code to remove the number and any following commas.

 

[Eric W]

I assume the comma is the delimiter, and the spaces are not part of the values (or in another way, the delimiter is ", "). So try:

Function Uniques(Text As String) As String
  Dim X As Long, Data() As String
  Data = Split(Text, ",")
  With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For X = 0 To UBound(Data)
      .Item(Trim(Data(X))) = 1
    Next
    Uniques = Join(.keys, ", ")
  End With
End Function

 

[mohadin]

Hi
Ty

Function Uniques(Text As String) As String
  Dim X As Long, Data() As String
  Data = Split(Text, ",")
  With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For X = 0 To UBound(Data)
      .Item(Trim(Data(X))) = 1
    Next
    Uniques = Join(.keys, ",")
  End With
End Function

 

[mohadin]

What
Seems same some how
Sorry Eric

 

[Eric W]

No worries, mohadin! We both analyzed it and came up with the same solution. I'm sure you posted yours before you had seen that I had posted mine. At the very least, it's a validation of the basic concept.

 

[bioplz]

I assume the comma is the delimiter, and the spaces are not part of the values (or in another way, the delimiter is ", "). So try:

Function Uniques(Text As String) As String
  Dim X As Long, Data() As String
  Data = Split(Text, ",")
  With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For X = 0 To UBound(Data)
      .Item(Trim(Data(X))) = 1
    Next
    Uniques = Join(.keys, ", ")
  End With
End Function

Beautiful. Thank you.

 

[Eric W]

Glad we could help.