duplicates in list

I need a code that will find an item that is most repeated in the list, if any item is repeated. If two items are repeated the same amount, I need to know both those items. How can I do this in a listbox?

This probably isnt the best way to do it, but it should serve your needs...


option Explicit

private Type DupList
Value as string
Count as Integer
End Type

private Sub Form_DblClick()
GetDuplicates List1
End Sub

private Sub Form_Load()
With List1
.AddItem "John"
.AddItem "John"
.AddItem "Karen"
.AddItem "Karen"
.AddItem "John"
.AddItem "Steve"
.AddItem "John"
.AddItem "Karen"
.AddItem "John"
.AddItem "Karen"
.AddItem "Karen"
End With
End Sub

Sub GetDuplicates(lstBox as ListBox)
Dim sTemp as string
'Dim sDups() as string
Dim iCount as Integer
Dim iMaxCount as Integer
Dim iTotal as Integer
Dim x as Integer
Dim j as Integer
Dim sDups() as DupList

for j = 0 to List1.ListCount - 1
sTemp = List1.List(j)
for x = j to List1.ListCount - 1
If sTemp = List1.List(x) then
iCount = iCount + 1
End If
next x

If iCount > iMaxCount then
iMaxCount = iCount
ReDim sDups(0)
sDups(0).Value = sTemp
sDups(0).Count = iMaxCount

ElseIf iCount = iMaxCount then
iTotal = UBound(sDups) + 1
ReDim Preserve sDups(iTotal)
sDups(iTotal).Value = sTemp
sDups(iTotal).Count = iMaxCount

ElseIf iCount < iMaxCount then
'do nothing it wasn't as much
End If

'clear out the counter
iCount = 0

next j

for j = 0 to UBound(sDups())
MsgBox sDups(j).Value & " : " & sDups(j).Count
next j

End Sub




I used a UDT only because I am not terribly strong with multi-dimension arrays. To use this, just place a listbox on a form named List1 and hit F5. it will pop up two message boxes saying "John : 5" and "Karen : 5". You can comment out one of the values making only one value the most repeated.

Hope this helps,
John


John Pirkey
MCSD
www.ShallowWaterSystems.com