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
like any suggestions. Please help me.....

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

ListRange.AdvancedFilter Action:=xlFilterCopy, _
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

OzGrid Business Applications

Posted by Duane Kennerson on April 20, 2001 2:26 PM

With a combination of the 2 ideas and a little tinkering, I was able to get
it to work, thanks again.
DaK Hi Duan 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 ListRange.AdvancedFilter Action:=xlFilterCopy, _ 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