Thread: Eliminate duplicates in comma sep strings

Good day, all...

I'm trying to find a way to eliminate duplicate values in a comma separated string. For example, in cell "F1" I have the following text:

EMAIL, BULLETIN BOARDS, EMAIL, MESSAGING, RETRACT MESSAGES, SCROLLING MESSAGES, RETRACT MESSAGES

And I'd like to end up with

EMAIL, BULLETIN BOARDS, MESSAGING, RETRACT MESSAGES, SCROLLING MESSAGES
** the second instance of "EMAIL" and "RETRACT MESSAGES" have been removed.


I have nearly 3,750 rows to go through, ranging from 2 to 88 phrases.

I suppose I could do a text to columns, and have 1 phrase per cell, then alphabatize horizontally, then run a loop through each of the cells, but that seems horribly inefficient.

If any of you could lend a hand, I'd appreciate whatever guidance you're offering.

Thanks!

This code will provide the cleaned up text in column G

Private Sub Cleanup()

   Dim c As Range
   Dim a As Variant
   Dim i As Long
   Dim result As String
   
   Set c = Range("F2")
   Do While c <> ""
   
      a = Split(c, ",")
      result = ","

      For i = LBound(a) To UBound(a)
         If InStr(1, result, a(i)) = 0 Then
            result = result & a(i) & ","
         End If
      Next i

      ' Lop off leading and trailing commas
      result = Mid(result, 2, Len(result) - 2)
   
      c.Offset(0, 1) = result
      
      Set c = c.Offset(1, 0)
      
   Loop

End Sub

hi, again, I know the thread is marked as solved and the offered code is good I would not say it is efficient in terms of time to process test file data attached, please check attachment and run code "test".

Time to process attached file example - 5751 rows of data:

Offered code: 26,7 seconds
My code: 2,8 seconds

If you are interested in analyzing the offered codes you'll find only two small things (apart from the main part - finding unique values) not used in one of them but they do make a big difference at the end. If to apply them to 6StringJazzer code you will get better results than both results provided above. To keep the intrigue I will leave codes analysis to you.