Can someone explain how this is working?

[aaronb50]

About a year ago a wrote a program and used the code below. I need it again for a new program but looking at it now that I kind of know what’s going on, the code just doesn’t make sense to me. I don’t understand how it’s working. It is working though, been used hundreds of times in the last year so it is working, I just don’t see how.
Basically I fill an array and then dump that array into a ListBox. From there I run the code below to get rid of any duplicated data now in the ListBox.

For i = 0 To List13.ListCount - 1
For x = 0 To List13.ListCount - 1

If Me.List13.ItemData(i) = Me.List13.ItemData(x) And x <> i Then
List13.RemoveItem i
End If

Next x
Next I


Works great and gets rid of all the duplicates.
But my issue is trying to understand why, if “x” and “I” are always = to each other, is it not getting rid of every line of data?

Is it the "And x <> i"? I don't really understand what that is doing.

 

[aaronb50]

Also I just realized.........

The code in the old program worked when I had the possibility of the same data located only twice in the Listbox.
This new program can have the same data many many times on the Listbox. That code is not getting rid of all of them. I have to run it 3 times back to back to get rid of all of them. Is there a better way to do this?

 

[gemma-the-husky]

if its in a query, then sort the query, and use this this sort of logic

 open a recordset based on the query

  while not end of recordset
    if this item is same as last item then
        delete this item
    else
        set last item equal to current item
    end if
   goto next item
 wend

 

[aaronb50]

[FONT=EX_CFF_Tahoma]There you go!![/FONT]
[FONT=EX_CFF_Tahoma]Now that I’m looking at your example I’m remembering I have done this in other places as well.[/FONT]
[FONT=EX_CFF_Tahoma]But instead of placing the data into the Listbox and then trying to get rid of duplicates and then loading what’s left back into an array, I took the original data and put it in alphabetical numerical order while it was still in the array and then loaded it into the Listbox and then did what you are suggesting. [/FONT]
[FONT=EX_CFF_Tahoma]Assign the first data item to an array and then compare it with the next.[/FONT]
[FONT=EX_CFF_Tahoma]If it’s the same then just delete it if it’s not then add to the array and keep going till the Listbox is empty.[/FONT]
[FONT=EX_CFF_Tahoma] [/FONT]
[FONT=EX_CFF_Tahoma]Thanks!!!!![/FONT]
[FONT=EX_CFF_Tahoma]Still not sure how that other code was doing what it was doing though. It just does not make sense!!![/FONT]

 

[BigHappyDaddy]

AaronB50,

Your code is a nested loop, or a loop within a loop. The outer loop is "i" and the inner loop is "x". For each increment of the outer loop i, the inner loop x has completed a complete cycle, or in this case from 0 To List13.ListCount - 1.

If Me.List13.ItemData(i) = Me.List13.ItemData(x) And x <> i Then
List13.RemoveItem i
End If

The above code is comparing the values stored at each index i and x. And comparing the values of i to x. So if the values are the same (you found a duplicate) and the indexes are different (if same index, then not a duplicate, comparing value to itself), then remove the list item.

This is where the code fails when there are multiple duplicates. By removing the item, the list has shorten by one. Now the index points to the next value in the list. But the next line of code increments X. So now X is pointing to the next value after that. You have totally skipped checking the new value at the original index X. If that new value is the same as the value of the List at I, the duplicate will remain.

Does that help?

 

[BigHappyDaddy]

Also, your code isn't the most efficient method. The code ends up repeating a lot of the comparisons.

 

[aaronb50]

Oh duh!!!!!!!!!!!!!

I see it now. Yeah the x is looping all the way through each time i moves to the next number. And I see why its not getting all of them.

Thanks Big!!