Why the different answers? (1 Viewer)

[wackywoo105]

I want to remove duplicates from an array. I came up with my own method before finding one, which involved setting duplicate entries 0 and just ignoring them.

Mine was:

NumberList() = Split(NumbersString, ",")

For x = 0 To UBound(NumberList())
For j = 0 To UBound(NumberList())

If Not x = j Then
If NumberList(x) Like NumberList(j) Then NumberList(j) = 0
End If

Next j
Next x

Dim countB As Integer
countB = 0
For x = 0 To UBound(NumberList())
If Not NumberList(x) Like "0" Then countB = countB + 1
Next x

MsgBox countB

The above is messy so I searched and found the function below:

Dim DeDupeArray() As Variant
Dim oDict As Object, i As Long
Set oDict = CreateObject("Scripting.Dictionary")
For i = LBound(NumberList) To UBound(NumberList)
    oDict(NumberList(i)) = True
Next
DeDupeArray = oDict.keys()
  
MsgBox UBound(DeDupeArray)

My concern is my method shows a count of 960 but the found method shows a count of 959. No "0"s should be included in my function count, so they should both show the same number of individual entries. Can anyone tell me why the difference of 1?

 

[arnelgp]

You ahould use:

oDict.Count

Since your array is 0 based. You have correct value (0 to 959) = 960.

 

[sonic8]

I quickly guess, your NumbersString has a superfluous comma at then end, which causes an empty item in the array, which your code counts but the found code does not.

 

[arnelgp]

dictionary Items/Keys collection is 0 based.

Dim DeDupeArray As Variant
DeDupeArray = oDict.keys()
MsgBox UBound(DeDupeArray)
MsgBox DeDupeArray(0)
MsgBox DeDupeArray(Ubound(DeDupeArray))

 

[June7]

First procedure is reporting an incremented counter in MsgBox, second procedure is reporting array UBound(). The UBound from first procedure would be the same.

Where did you find the second code snippet? I am not seeing it removing duplicates. Not showing the function declaration nor how result is returned. Looks like didn't post all the code.

 

[wackywoo105]

Dim NumberList() As String
Dim NumbersString As String

*/ repeated several times to build up string of numbers. no comma at end /*
If NumbersString Like "" Then NumbersString = Left(Replace((rs![Mobile No]), " ", ""), 11) Else NumbersString = NumbersString & "," & Left(Replace((rs![Mobile No]), " ", ""), 11)
*/ end of string creation /*

NumberList() = Split(NumbersString, ",")

I have posted the 2 functions in full. The DeDupeArray function returns 959 and mine 960. If I run my function to create "0" entries in place of duplicates and then run DeDupe, it the returns 960, which I guess is because there in now a "0" in the data along with all the numbers. It's only when used on its own it returns 959, so I cant figure out why my function is finding one more entry as I ignore any "0" when counting entries.

 

[arnelgp]

again, arrays are 0 based.

so from this code:

Dim DeDupeArray() As Variant
Dim oDict As Object, i As Long
Set oDict = CreateObject("Scripting.Dictionary")
For i = LBound(NumberList) To UBound(NumberList)
    oDict(NumberList(i)) = True
Next
DeDupeArray = oDict.keys()
MsgBox UBound(DeDupeArray)

'add this line

dim j as integer
i=0
for j=lbound(DeDupeArray) to ubound(DeDupeArray)
   i=i+1
next
msgbox "i, count is : " & i

 

[sonic8]

I have posted the 2 functions in full.

You didn't. The procedure declaration is missing, so we must guess what the arguments, the return value and their data types are.

For further diagnosis, I would suggest you create a simple example with a hard-coded string, containing data for relevant test case with very few items instead of 960. Modify the input data until you can reproduce the issue with different results from the two functions. It then should be relatively easy to identify which case causes the differences.

 

[June7]

Did not post complete procedures. I already explained in post 5 why the two MsgBox outputs are different.

 

[sonic8]

I am not seeing it removing duplicates.

They keys of the dictionary must be unique. Nevertheless, I also don't fully understand how the second example is supposed to work. I would guess it should raise an runtime error.
In any case, the function you are suspecting not to remove duplicates is returning a lesser count than the one, which does.

 

[wackywoo105]

Many thanks for the replies. Of course I am being stupid. The count for my function starts at 1 whereas as mentioned the array starts at 0, so will always be 1 less than my method. I think running DeDupe after mine with it counting in a "0", thus returning the same count number was throwing me off. I tried it with NumbersString = "123,234,345,456,567,123,234,345,456,567" and it works fine.