VBA - Remove duplicates from delimited string

Hi All,

I am trying to remove duplicates from a delimited string in VBA.

I have the following code (with the test at the top), but it returns (what appear to be) inconsistent results from the two test strings - the first one being the bigger issue since it still has duplicates.  I had thought that the keys in a scripting.dictionary had to all be unique, but obviously I am misunderstanding something.

Option Explicit

Sub Test()

Debug.Print RemoveDuplicates("Alan; Bob; Alan", ";")
Debug.Print RemoveDuplicates(" ;Alan; Bob;Alan; Bob;", ";")

End Sub


Function RemoveDuplicates(sDelimitedStringWithDuplicates As String, sDelimiter As String) As String

Dim vElement As Variant
Dim sdObj As Object

    Set sdObj = CreateObject("Scripting.Dictionary")
    
    For Each vElement In Split(sDelimitedStringWithDuplicates, sDelimiter)
    
        sdObj.Item(vElement) = Trim(CStr(vElement))
    
    Next vElement
    
    RemoveDuplicates = Join(sdObj.keys, sDelimiter)
    
    Set sdObj = Nothing

End Function

Open in new window


Any suggestions welcome.

For the avoidance of doubt, speed is not a practical issue here - the strings are all fairly short even with the duplicates in them.

Thanks,

Alan.
LVL 23
AlanConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

käµfm³d 👽Commented:
You're trimming the value, but you're not trimming the key. This means you have "Alan" and " Alan" (note the leading space) as keys. Try trimming the key also.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
Function RemoveDuplicates(sDelimitedStringWithDuplicates As String, sDelimiter As String) As String

Dim strParts() As String
Dim lngIndex As Long
Dim col As New Collection

    strParts = Split(sDelimitedStringWithDuplicates, sDelimiter)
    
    On Error Resume Next
    For lngIndex = 0 To UBound(strParts)
    
        col.Add Trim(strParts(lngIndex)), CStr(Trim(strParts(lngIndex)))
    
    Next
    
    For lngIndex = 1 To col.Count
    
        If lngIndex < col.Count Then
            RemoveDuplicates = RemoveDuplicates & col(lngIndex) & sDelimiter
        Else
            RemoveDuplicates = RemoveDuplicates & col(lngIndex)
        End If
    Next

End Function

Open in new window

0
Randy PooleCommented:
Function RemoveDuplicates(sDelimitedStringWithDuplicates As String, sDelimiter As String) As String
  Dim mArr as Variant
  mArr=Split(sDelimitedStringWithDuplicates,sDelimiter)
  Dim c as Integer
  Dim s as String
  Dim rs as String
  rs=sDelimiter
  for c=LBound(mArr) to UBound(mArr)
    s=Trim(mArr(c))
    if s<>"" then if instr(1,rs,sDelimiter & s & sDelimiter)=0 then rs=rs & s & sDelimiter
  Next c
  RemoveDuplicates=rs
End Function

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Martin LissOlder than dirtCommented:
Randy I didn't test that but wouldn't it find that "sam" was in "samuel" and treat them as the same?
0
Randy PooleCommented:
No, because on the instr is places a delimiter at the front and end of the word
0
AlanConsultantAuthor Commented:
Hi Guys,

It just required trimming the key as per Kaufmed's first reply.

For those that may wish to follow later, this works (at least for the three test cases identified, and it doesn't matter in my case whether there are leading and trailing delimiters but it would be trivial to remove those if required).  

Option Explicit

Sub Test()

Debug.Print RemoveDuplicates("Alan; Bob; Alan", ";")
Debug.Print RemoveDuplicates(" ;Alan; Bob;Alan; Bob;", ";")
Debug.Print RemoveDuplicates("Alan; Bob; Bobby-Alan; Alan", ";")

End Sub


Function RemoveDuplicates(sDelimitedStringWithDuplicates As String, sDelimiter As String) As String

Dim vElement As Variant
Dim sdObj As Object

    Set sdObj = CreateObject("Scripting.Dictionary")
    
    For Each vElement In Split(sDelimitedStringWithDuplicates, sDelimiter)
    
        sdObj.Item(Trim(CStr(vElement))) = Trim(CStr(vElement))
    
    Next vElement
    
    RemoveDuplicates = Join(sdObj.keys, sDelimiter)
    
    Set sdObj = Nothing

End Function

Open in new window


Some points to MartinLiss for suggesting another test case too.

Thanks everyone,

Alan.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.