That is considerably faster (thanks for the exit for tip), unfortuantly
the adult and student counter no longer works right, it will only give
a 1 or 0. Any ideas why? Thanks.

- David
Jim Cone wrote:
David,
This should be considerably faster.
Note the "Exit For".
I may have overlooked something but
I can't test it, you will have to try it out...
'------------
ReDim data(1 To nodupes.Count, 1 To 4)
On Error Resume Next
For Each Item In nodupes
f = f + 1
For c = 1 To 669
If ActiveSheet.Cells(c, 1).Value = Item Then
If ActiveSheet.Cells(c, 3) = "Adult" Then
d = d + 1
ElseIf ActiveSheet.Cells(c, 3) = "Student" Then
s = s + 1
End If
data(f, 4) = ActiveSheet.Cells(c, 5).Value
Exit For
End If
Next 'C

data(f, 1) = Item
data(f, 2) = d
data(f, 3) = s
d = 0
s = 0
Next 'Item
ListBox1.List = data
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"David"
wrote in message
I have a spreadsheet full of data in this format for a theatre i work
at. Because there are multiple entries for one person I load all the
names into a collection and then use this function (below) to load them
into a listbox. While this method does work it quickly becomes very
slow as we have hundreds of costumers. I was hoping someone could help
me speed it up. Thanks

last name, first name --- seat number ---- adult or student ticket ----
paid or not paid

ReDim data(1 To nodupes.Count, 1 To 4)
On Error Resume Next
For Each Item In nodupes
f = f + 1
For c = 1 To 669
'For r = 1 To 28
If ActiveSheet.Cells(c, 1).Value = Item And
ActiveSheet.Cells(c, 3) = "Adult" Then
d = d + 1
ElseIf ActiveSheet.Cells(c, 1).Value = Item And
ActiveSheet.Cells(c, 3) = "Student" Then
s = s + 1
End If
data(f, 1) = Item
data(f, 2) = d
data(f, 3) = s
'data(f, 4) = ActiveSheet.Cells(c, 5).Value
Next
For c = 1 To 669
If ActiveSheet.Cells(c, 1).Value = Item Then
data(f, 4) = ActiveSheet.Cells(c, 5).Value
End If
Next
d = 0
s = 0
Next Item
ListBox1.List = data

- David