The code I gave you will give a list of unique names ( the collection nodupes). You could loop through that collection to print out a list of the unique values.

Similarly, you could use that to get the name, and then do a countif to get the number of times that name appears. Take 1 from that count and you will get the name and the number of duplicates.

The while - wend section builds the list of unique names.

You can loop through that in the form

for each item in nodupes

next item

the variable item will give the value of the name and it can be used in a countif function.


Tony

----- wrote: -----

This comes close but not quite..

Mike H
Mike H
Jim B
Jim B
Jim B
Bill C
Tom R
Tom R

The result I would want is Mike H is duplicated once, Jim
B duplicated twice, Tom R duplicated once so I have 3
duplicates. The other option would be to count only
unique names..

If I can get help with code both ways I would be estatic..
Thank you.
-----Original Message-----
Hi
If I have understood how you want to count things, then
this should give you the count. Assume that the data is
a,a,b,b then the output you would want is 2 (ie there are
2 duplicate names). Similarly if the data is a,a,b,b,b
then the result is 3.
Tony
Sub aaa()
Dim nodupes As New Collection
countall = 0
Range("a3").Select
On Error Resume Next
While Not IsEmpty(ActiveCell)
nodupes.Add ActiveCell.Value, CStr(ActiveCell.Value)
countall = countall + 1
ActiveCell.Offset(1, 0).Select
Wend
On Error GoTo 0
Range("D3").Value = countall - nodupes.Count
End Sub
.