Best method to compare two listbox contents?

[jawf]

I have two listboxes that have text items in them. I want to look to see if each item in listbox1 is present in listbox2 and if not then report out. List box2 may have many more items than listbox1. I can do this with nested For...Next loops, but is that the most efficient manner? Listbox1 may only have 100 items and listbox2 may have 3000.

Thanks, Joe

[Ed Jobe]

There's nothing wrong with the For...Next approach. If its fast enough to suit you. If not you might try putting the large list in a collection. The reason is that a collection maintains an index on the items inside. If you try to access an item not in the list, an error will be generated. When an index is used in this way, the entire list doesn't have to be iterated. All you need is an error handler that traps the error and gives an indication that the item is not in the list and then returns to normal program flow.

[MikeJarosz]

When I intend to populate a list I first make an array in memory. I would not try to manipulate a list of data from the form object.

Finding an item in one list from another list is a classic computer algorithm called a binary search. Trouble is, VBA doesn't provide a built in sort, which is necessary to perform this technique. You could write your own, but I think that is probably beyond the scope of many readers of this forum.

Those who are interested can look up bubble sort and binary search in google. This is my favorite research tool. Very often I find code I can use without any modification.

[jawf]

There's nothing wrong with the For...Next approach. If its fast enough to suit you. If not you might try putting the large list in a collection. The reason is that a collection maintains an index on the items inside. If you try to access an item not in the list, an error will be generated. When an index is used in this way, the entire list doesn't have to be iterated. All you need is an error handler that traps the error and gives an indication that the item is not in the list and then returns to normal program flow.
Thanks, Ed. That makes sense, but I tested and the For...Next is going to be fast enough. Since I am discovering items in Listbox1 that are not in Listbox2, I want to remove the incorrect items from Listbox1's list. If I do this as they are found, it messes up the list and I skip the very next item. I was wondering if I could populate a dynamic array with the findings and then use that to go back and remove the contents of the array from the listbox1 list?

Thanks, Joe

[Ed Jobe]

If you put them in a collection, you don't have to keep track of the count and you can just use For..Each when doing the cleanup.

[jawf]

Thanks, I'll give it a shot.

Joe