Remove Repeat Numbers from string

[craigachan]

I'm pulling my hair out and need help.

I have a string of numbers separated by commas. I'm looking to remove any number that repeats.

Examples:
1,2,3,2 returns 1,2,3
3,4,5,3,6 returns 3,4,5,6
3,4,3,5,3,6 returns 3,4,5,6

This all works fine, but when I try 8,9,8,9 I get '8'. but I want 8,9

It seems to work with 3 numbers but not with 2.

I can't seem to figure out how to fix this. would appreciate someone looking at my code and giving solution, fix, or input.

Public Function RemoveDblNums(strNumbers As String)
 
    strNumbers = SortDelimitedStringOfNumbers(strNumbers, ",") 'Sort Numbers first
    Dim splitarray() As String
    Dim i As Integer
    Dim x As Integer
    Dim N As String     'Number being tested
    Dim strFinal As String
 
    strFinal = ""
    splitarray = Split(strNumbers, ",")
    For i = 0 To UBound(splitarray)
        For x = (i + 1) To UBound(splitarray)
 
                If splitarray(i) <> splitarray(i + 1) Then
                    N = splitarray(i)
                Else
                    N = "dup"
                End If
        Next x
 
        If N <> "dup" Then
                    If strFinal = "" Then
                        strFinal = splitarray(i)
                    Else
                        strFinal = strFinal & "," & splitarray(i)
                    End If
        End If
 
    Next i
    RemoveDblNums = strFinal
 
End Function

Sort code

Public Function SortDelimitedStringOfNumbers(pInString As String, pDelimiter As String) As String
Dim i As Long, ii As Long
Dim temp
Dim strParts() As String
strParts() = Split(pInString, pDelimiter)
For i = 1 To UBound(strParts)
   strParts(i) = Trim(strParts(i))
Next
 
For i = 0 To UBound(strParts)
    For ii = i To UBound(strParts)
        If Val(strParts(i)) > Val(strParts(ii)) Then
        temp = strParts(ii)
        strParts(ii) = strParts(i)
        strParts(i) = temp
    End If
    Next
Next
SortDelimitedStringOfNumbers = Join(strParts, pDelimiter)
End Function

Thanks

 

[Rabbie]

I have done some testing of this and it is loosing the highest number in the string if it is a duplicate.

ie

?RemoveDblNums("1,2,2,3,4,5,4,5") gives 1,2,3,4 and not 1,2,3,4,5

You need to check that part of your code.

 

[RuralGuy]

I would probably look hbere:
For i = 0 To UBound(splitarray)
...s/b
For i = 0 To UBound(splitarray)-1

 

[craigachan]

The problem I have is that If I fix the code to accept the highest number that repeats, it drops the lowest number that repeats. It seems I can't have both. Perhaps I've been working on this one too long.

 

[RuralGuy]

Perhaps I've been working on this one too long.

I agree. Take a break. Go have a cup of coffee and maybe take a walk or a nap. I have a feeling some people here will be looking at the code. Are you looking Bob?

 

[boblarson]

This would seem to work better:

Public Function RemoveDblNums(strNumbers As String)
    Dim splitarray As Variant
    Dim i As Integer
    Dim x As Integer

    Dim strFinal As String
    
    strNumbers = SortDelimitedStringOfNumbers(strNumbers, ",") 'Sort Numbers first

    splitarray = Split(strNumbers, ",")
    For i = 0 To UBound(splitarray)
        If InStr(1, strFinal, splitarray(i), vbTextCompare) = 0 Then
            If strFinal = "" Then
                strFinal = splitarray(1)
            Else
                strFinal = strFinal & "," & splitarray(i)
            End If
        End If
    Next i
    RemoveDblNums = strFinal

End Function

 

[boblarson]

I had to add a line to the code above because I forgot it would have added a comma at the beginning unless I had this in there:

If strFinal = "" Then

 

[raskew]

Hi -

Here's a 'dinosaur version', written in A97 without benefit of the Split() function (or a suitable workaround), and without using arrays.

Public Function ElimDupNums97(pStrIn As String, pDelim As String) As String
'*********************************************
'Purpose:   Eliminate duplicate numbers from
'           a string, without using a Split()
'           function lookalike.
'Coded by:  raskew
'Input:     from debug (immediate) window:
'           ? ElimDupNums97("12, 4, 1, 12,8,10,1", ",")
'Return:    4,12,8,10,1
'*********************************************

Dim strHold  As String
Dim strTempL As String
Dim strTempR As String
Dim strOut   As String

   strHold = pStrIn

   Do While InStr(strHold, pDelim) > 0
      strTempL = Left(strHold, InStr(strHold, pDelim) - 1)
      strTempR = Trim(Mid(strHold, InStr(strHold, pDelim) + 1))
      strOut = strOut & IIf(InStr(strTempR, strTempL) = 0, strTempL & pDelim, "")
      strHold = strTempR
   Loop

   ElimDupNums97 = strOut & strHold

End Function

Now, that's pretty simple and seems to work well. The real challenge is to devise a method to sort the output, once again without using arrays. I'm still struggling with that.

Bob

 

[gemma-the-husky]

a) how big are the string
b) are the numbers in a limited range

there are lots of solutions already posted, but depending on the above, you may decide a brute force approach is not much less efficient than trying to find something elegant

eg bubblesort is less efficient than quicksort, but it certainly doesnt matter for a small number of items to sort.

 

[craigachan]

Gemma - there can be up to 32 numbers no including repeats

Boblarson - your solution seem to drop the first number

Here is what I think is the solution

Public Function RemoveDblNums(strNumbers As String)
 
    strNumbers = SortDelimitedStringOfNumbers(strNumbers, ",") 'Sort Numbers first
    Dim splitNumbers() As String
    Dim i As Integer
    Dim strFinal As String
 
    strFinal = ""
    splitNumbers = split(strNumbers, ",")
    For i = 0 To UBound(splitNumbers)
        Forms!Test![Ubound] = UBound(splitNumbers)
            If strFinal = "" Then
                strFinal = splitNumbers(i)
            ElseIf splitNumbers(i) <> splitNumbers(i - 1) Then
                strFinal = strFinal & "," & splitNumbers(i)
            End If
    Next i
 
    RemoveDblNums = strFinal
 End Function

But here is something interesting that I don't understand. The string is sorted into increasing numbers first. Then notice the 'ElseIf' line

'ElseIf splitNumbers(i) <> splitNumbers(i - 1) Then'

When I tried greater than instead of <> I got

1,2,3 returns 1,2,3
2,5,6,6 returns 2,5,6

but
2,3,23 return 2,3
2,3,23,24 returns 2,3,24

It all works fine with '<>' however.

Can anyone tell me why this is?

 

[raskew]

Hi -

Using your combination, the same thing happened with my solution. Think we're going to find that the problem is that the numbers e.g. '23', are being processed as strings, not integers. I'll play with it to see if I can verify/correct this.

Example:
x = 23
y = 2
? instr(x, y)
1

when what we need is:
? x = y
False

Bob

 

[gemma-the-husky]

I heart recursion

I bet there's a neat way of doing this with an elegant but stack intensive recursive solution. I may have a go this evening!

 

[Brianwarnock]

OK try

Public Function RemoveDblNums(strNumbers As String)
    Dim splitarray As Variant
    Dim i As Integer
    Dim x As Integer

    Dim strFinal As String
    
    strNumbers = SortDelimitedStringOfNumbers(strNumbers, ",") 'Sort Numbers first

    splitarray = Split(strNumbers, ",")
    For i = 0 To UBound(splitarray)
        If InStr(1, strFinal, splitarray(i), vbTextCompare) = 0 Then
            If strFinal = "" Then
                strFinal = splitarray([COLOR="Red"]i[/COLOR])
            Else
                strFinal = strFinal & "," & splitarray(i)
            End If
        End If
    Next i
    RemoveDblNums = strFinal

End Function

Brian

 

[boblarson]

Boblarson - your solution seem to drop the first number

Not in any of my testing.

 

[RuralGuy]

Craig,
You need to be congratulated for this post. It is giving these guru's a ton of fun! They will get it worked out and we will all learn from the experience.

 

[Brianwarnock]

After the change I made , in red, I'm having no problems.


fnum ..................Fnumedited
1,2,3,2................1,2,3
1,23,2,3,23,2........1,2,3,23
1,3,2,4,3,4...........1,2,3,4
1,2,3,4,23,24,23.. 1,2,3,4,23,24

Brian

 

[boblarson]

Yeah, I see the problem in that my code had 1 instead of i (hate it when keyboards do that to you )

 

[craigachan]

Raskew - I think you are right. I changed the ElseIf line from:

ElseIf splitNumbers(i) > splitNumbers(i - 1) Then

to

ElseIf Val(splitNumbers(i)) > Val(splitNumbers(i - 1)) Then

And it started working. I haven't tried it extensively but it seems to work for a short list of numbers.

Thanks everyone for the thought process.

 

[craigachan]

Boblarsen - I didn't notice the (1) either. Also good solution. Thank you.

Raskew - I think you are right. I changed the ElseIf line from:

ElseIf splitNumbers(i) > splitNumbers(i - 1) Then

to

ElseIf Val(splitNumbers(i)) > Val(splitNumbers(i - 1)) Then

And it it seems to work.

I haven't tested it extensively yet but seems to work for shorter strings of numbers. Let me know, anyone, if it fails in your own testing.

Thanks for all the input and help.

 

[gemma-the-husky]

I heart recursion

I bet there's a neat way of doing this with an elegant but stack intensive recursive solution. I may have a go this evening!

Having thought about it, i dont think recursion IS the right approach!