Counting duplicate numbers

Posted by Duane Kennerson on April 18, 2001 3:33 PM

I hope I can explain this. I need excel to look at a range
of numbers and tell me how many of a certain number appears
in the range. For example:
Range A1:A5 contains the numbers 5,6,7,5,8 with one number in each
cell. I would want excel to tell me that there are 2-5's, 1-6, 1-7
and 1-8. Its basically looking at the numbers and telling
me how many of each appear in the range. Does this make sense?
If it can be done without VBA that would be preferred but I would

Posted by Ian on April 18, 2001 4:02 PM

One way

Forgive me if I'm misunderstanding something, but you could you simply sort the data and use the subtotal function using the "count" capability. This would seem to give you your list? Not very flashy, but it should work.

Good luck.

Posted by Dave Hawley on April 18, 2001 9:09 PM

Hi Duan

Here is a VBA method. To use it select the data in your column and make sure the 2 columns to the right are empty. Then simply run it.

Sub CountOfEachItem()
'Written by OzGrid Business Applications
'www.ozgrid.com

Dim ListRange As Range
Dim NewList As Range
Set ListRange = Selection
ListRange.Offset(0, 1).Clear
ListRange.Offset(0, 2).Clear

CopyToRange:=ListRange.Offset(0, 1).Cells(1, 1) _
, Unique:=True

Set NewList = ListRange.Offset(0, 1).SpecialCells(xlConstants)
NewList.Offset(0, 1).FormulaR1C1 _
= "=COUNTIF(" & ListRange.Address(ReferenceStyle:=xlR1C1) & " C[-2],RC[-1])"

Set NewList = Nothing
Set ListRange = Nothing
End Sub

...Or to do it without VBA. Assuming your list is in Column A

Select any cell in your data list and Go to Data>Filter>Advanced Filter.
Select "Copy to another location"
Make sure you list range is correct
Put B1 in "Copy to"
Check "Unique records only"
Click OK.

Now in in cell C1 put:
=COUNTIF(A:A,B1)
Copy down

Dave