Count duplicates needed

  • Thread starter Thread starter WylieC
  • Start date Start date
W

WylieC

I have a list of names starting in R3C1 that may contain
duplicate names in column 1 only. I need some help with
the code to search all cells starting at R3C1 until the
names end, tally the count of duplicate names and put the
result in R3C4.
Thank you very much for your help.
 
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
 
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
Click to expand...
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.
 
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

----- (e-mail address removed) 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
Click to expand...
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.
 
Back
Top