Remove duplicates from a string...

[Inquisitor99]

Does anyone know a quick and efficient method to strip duplicates from a string?

For example:

My string = "AB, CD, AB, EF"

My string without duplicates = "AB, CD, EF"

Thanks

 

[MajP]

Public Function getNoDupString(strDupString As String)
  Dim strDupArray() As String
  Dim intCounterI As Integer
  Dim intCounterJ As Integer
  Dim intFirstPos As Integer
  strDupArray = split(strDupString, ",")
  For intCounterI = 0 To UBound(strDupArray)
     For intCounterJ = intCounterI + 1 To UBound(strDupArray)
       If Trim(strDupArray(intCounterJ)) = Trim(strDupArray(intCounterI)) Then
         strDupArray(intCounterJ) = ""
       End If
     Next intCounterJ
  Next intCounterI
  For intCounterI = 0 To UBound(strDupArray)
    If Not strDupArray(intCounterI) = "" Then
      getNoDupString = getNoDupString & strDupArray(intCounterI) & ","
    End If
  Next intCounterI
  If Right(getNoDupString, 1) = "," Then
    getNoDupString = Left(getNoDupString, Len(getNoDupString) - 1)
  End If
End Function

 

[UnicornRainbow]

I've had good results with

Function noDups(myStr As String) As String
Dim myA() As String, myB As String, myI As Integer
myA = Split(Trim(myStr), ",")
For myI = LBound(myA) To UBound(myA)
If InStr(myB, LTrim(myA(myI))) = 0 Then
myB = myB & "," & myA(myI)
End If
Next myI
noDups = Mid(myB, 2)
End Function

 

[Inquisitor99]

Thanks MajP and UnicornRainbow.

I thought no one was going to respond and I was pressed for time, so I tried to come up with my own method using various string functions - not sure if it's the most efficient, but it does work. Here it is:

I am looping through a recordset to build my string.

myField = next field in the recordset
myString = string to remove duplicates from
mySeparator = comma between each item in the string


For each item in the recordset...

1) Save the field contents in another variable:

Temp = myField

2) Replace all occurrences of the current item in the recordset with spaces:

myString = Replace(myString, Temp & mySeparator, String(Len(Temp) + 1, Chr(32)))

3) Concatenate the current item to the string (now it is the only occurrence in the string because all the others have been replaced by spaces):

myString= myString& myField & mySeparator

4) Close the recordset

5) Replace all the spaces in the string with the empty string:

Temp = Replace(strConcat, Chr(32), "")

6) To create a space after each comma delimited item, replace all the commas with a "comma and a space":

Concatenate = Replace(Temp, ",", ", ")

-------------------------

I see that both of your methods use arrays. I will try your method, UnicornRainbow, as it appears to be very short and sweet.

Thanks.

 

[MajP]

It looks like you are building this string from a recordset. If that is the case could you just use a "SELECT DISTINCT" so you do not have any duplicates to start with?

 

[BoxHead]

The following will concatenate the field with no duplicates. May save a step.

Public Function ConcatField(MyFld As String, MyBreak As String, _
TblQryName As String, Optional MyCrt As String) As String
[COLOR=green]'***** Example:  ConcatField("[FieldName]",", ","TableName","[EmpId] = " & [EmpId])[/color]
Dim myString As String, strSql As String
Dim db As DAO.Database, rst As DAO.Recordset

If MyCrt = "" Then
strSql = "SELECT" & MyFld & " FROM " & TblQryName & ";"
Else
strSql = "SELECT " & MyFld & " FROM " & TblQryName & " WHERE " & MyCrt & ";"
End If

Set db = CurrentDb
Set rst = db.OpenRecordset(strSql, dbOpenDynaset)

Do Until rst.EOF
If InStr(myString, rst.Fields(0) & MyBreak) = 0 Then
myString = myString & rst.Fields(0) & MyBreak
End If
rst.MoveNext
Loop

If Len(myString) > Len(MyBreak) Then
ConcatField = Left(myString, Len(myString) - Len(MyBreak))
Else
ConcatField = myString
End If

End Function

HTH



When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[Inquisitor99]

MajP,

Sometimes the easiest solution is the most obvious. You are absolutely correct!!! All I had to do was use the DISTINCT option to filter out the duplicates. I don't know how I missed that. I think I've been working way too hard - it's time for a vacation

 

[TheAceMan1]

and this:

[blue]Public Function NoDups(ByVal usrStg As String)
   Dim Ary, Build, x As Integer, idx As Integer
   
   usrStg = Replace(usrStg, " ", "") 'Remove spaces
   Ary = Split(usrStg, ",")
   
   For x = LBound(Ary) To UBound(Ary)
      idx = InStr(1, usrStg, Ary(x))
      
      If idx Then
         Build = Build & Ary(x) & ","
         
         If InStr(idx + 1, usrStg, Ary(x)) Then 'Any Dups?
            usrStg = Replace(usrStg, Ary(x), "") 'Remove All!
         End If
      End If
   Next
         
   NoDups = Left(Build, Len(Build) - 1)
   
End Function[/blue]

See Ya! . . . . . .

 

[Inquisitor99]

Thanks for adding your version Aceman. I'm going to bookmark this page because now I have 5 different workable methods to strip duplicates from strings.