I want to find duplicate rows in a listbox, but based from multi-column value,
Dim source As Listbox = Listbox1
Dim ix, jx As Integer
For ix = source.ListCount-1 DownTo 0
For jx = ix + 1 To source.ListCount-1
If source.Cell(ix,0) = source.Cell(jx,0) then
if source.Cell(ix,1) = source.Cell(jx,1) then
if source.Cell(ix,2) = source.Cell(jx,2) then
if source.Cell(ix,3) = source.Cell(jx,3) Then
Listbox1.cell(jx,1)="duplicate"
end if
end if
end if
end if
Next
Next
when I do running the code, no error found but not executed.
Have a look at the inner 4 ifs and try to figure out where your values go into the “else”. Which you should add. Best try that with just a row or 2 in the listbox. Then please combine the inner 4 ifs to one line.
Public Sub FindDuplicates(lbSource as Listbox)
dim iMaxRow as Integer = (lbSource.ListCount - 2)
dim iMaxCol as Integer = (lbSource.ColumnCount - 1)
for iRow as Integer = 0 to iMaxRow
dim iNextRow as Integer = iRow + 1
for iColumn as Integer = 0 to iMaxCol
if lbSource.Cell(iRow, iColumn) <> lbSource.Cell(iNextRow, iColumn) then
// Not a match, check the next row
continue for iRow
end
next
// If we reach this point, the row is a duplicate
lbSource.Cell(iNextRow, 1) = "Duplicate"
next iRow
End Sub
Thanks, it works, but with the condition, If First Row and next rows have the same value.
The code doesn’t work if the duplicate rows in scramble position.
I mostly put an object-references in the rowtag of each row and use these objects for business and the listbox itself as just the view for the user. This makes live a lot easier and in some cases much faster.
To resolve this kind of troubles, I sorted my Listbox before applying the search duplicates. But that project was data base driven and I was able to sort more than one column.
To do it with the value of 4 columns (or more), concatenate their value in a hidden column, and sort on that column, then scan all rows and remove duplicates.
Chances are sort is way faster than trying to do it in code.
I created a simple app with a listbox and populated it with these data and ran your original code.
It DID work! However, I did not use a header in listbox.
I believe that your header is somehow getting in the way. Make sure that your code is not treating the header line as data and that your For…Next loops are starting and ending as you intend.
I thought that too, listboxes are just for viewing but I found sorting a 44,000 row listbox is much faster than any other method of sorting including arrays and SQL and requires just one line of code. I don’t know how they do it but it’s incredibly fast and shouldn’t be discounted as a viable solution based on the result.
I like using dictionaries for things like this. Works for any number of columns.
Public Sub FindDupeRows(lb as listbox)
dim d as new dictionary
dim s as string
dim dupes() as integer
dim u as integer = lb.ListCount-1
for r as integer = 0 to u
s=""
for c as integer = 0 to lb.ColumnCount-1
s=s+lb.cell(r,c) +"|"
next
if d.haskey(s) then
dupes.Append r
else
d.value(s)=r
end if
next
End Sub
The | delimiter prevents cases where a single column of “1234” would match columns of “1”,“2”,“3”,“4”
Now that you have an array of dupes, you can remove them.
for r as integer = ubound(dupes) downto 0
listbox1.RemoveRow dupes(r)
next