[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBA - Remove duplicates from delimited string

Posted on 2014-07-11
6
Medium Priority
?
655 Views
Last Modified: 2014-07-11
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.
0
Comment
Question by:Alan
6 Comments
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 1800 total points
ID: 40191597
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
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40191602
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
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40191604
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 50

Assisted Solution

by:Martin Liss
Martin Liss earned 200 total points
ID: 40191609
Randy I didn't test that but wouldn't it find that "sam" was in "samuel" and treat them as the same?
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40191629
No, because on the instr is places a delimiter at the front and end of the word
0
 
LVL 21

Author Closing Comment

by:Alan
ID: 40191630
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question