Listbox to Dictionary Remove Duplicates

General
1

I been trying for hours to wrap my head around this, it seems like it should be simple.

I want to read the rows of a listbox, that only contain 1 cell per row in to a dictionary as keys. Then delete the listbox rows and write the dictionary back to list row assuming the dictionary will not allow duplicate keys therefore removing all the duplicate rows from the listbox.

I tried so may variations of code it is not even funny, below is wrong I know but I wanted to show something of my thoughts. I know I need to be assigning the value as keys, but I have not found a proper way to do that and I looked at the docs until my eyes are blurred.


Dim i As Integer
Dim d As New Dictionary

For i = 0 To Listbox1.ListCount-1
  d.value(i) = listbox1.cell(i,0)
next

listbox1.deleteAllRows

for i = 0 to d.Count-1
  listbox1.addRow d.value(i).stringValue
next
2

you have it backwards :slight_smile:

Dim i As Integer
Dim d As New Dictionary

For i = 0 To Listbox1.ListCount-1
  d.value( listbox1.cell(i,0))=i // it could be ANY value, since all you care about is unique keys
next

listbox1.deleteAllRows

for each key as string in d.keys
  listbox1.addRow key
next
3

Lol the story of my life ( being backwards ). Thanks, but the code is giving an error on listbox1.addRow d.value(i).key saying the variant has no member named key

[quote=337218:@Dave S]you have it backwards :slight_smile:

[code]
Dim i As Integer
Dim d As New Dictionary

For i = 0 To Listbox1.ListCount-1
d.value( listbox1.cell(i,0))=i
next

listbox1.deleteAllRows

for i = 0 to d.Count-1
listbox1.addRow d.value(i).key
next
[/code][/quote]

4

look at the update… you got there faster than I thought :slight_smile:

for each key as string in d.keys
  listbox1.addRow key
next
5

Yeah I am watching the thread, after hours of getting nowhere on my own hoping one of you experts could tell me my error. Thanks again, still getting an errors this time…

for each key in d.keys
listbox1.addRow key

Says this item does no exist , referring to key in both lines

[quote=337220:@Dave S]For i = 0 To Listbox1.ListCount-1
d.value( listbox1.cell(i,0))=i // it could be ANY value, since all you care about is unique keys
next

listbox1.deleteAllRows

for each key in d.keys
listbox1.addRow key
next[/quote]

6

Ok I think I got it, thanks so much.

I am using…

for each key as variant in d.keys
  listbox1.addRow key.stringValue
next
7

Unless you specifically need the dictionary, you can also sort the listbox then cycle through rows from top to bottom, and when you find a duplicate of the row previously seen, remove the new one.

Public Sub RemoveDuplicates(extends LB as listbox) dim previousCell as String dim maxRow as Integer = LB.listcount-1 for row as integer = maxRow downto 0 if LB.Cell(row,0) = previousCell then LB.removeRow(row) else previousCell = LB.Cell(row,0) end if next End Sub

8

Is the listbox editable?
If not, then consider fixing the duplicates before you fill it

If it is filled from a database, use SELECT DISTINCT
If not, create your dictionary
Fill your dictionary
Then populate the listbox from the dictionary

9

Or just use an array of strings…

dim arr() as string For i = 0 To Listbox1.ListCount-1 Dim v as string = listbox1.cell(i,0) If arr.indexOf(v) = -1 then Arr.append v End if next

10

Thanks, everyone. The data comes from a user loaded text file. It can be a few lines to tens of thousands of lines. I had a slower way to find duplicates that were getting out of hand with larger text files. The dictionary turned what was taking minutes into a few seconds. Michel’s & Greg’s method looks interesting, but the dictionary seems very fast and works great for this. Still, will test out the other suggestions in this thread for educational sake.