How do I count the instances of numbers in a list?
 

I have a list of numbers, most duplicates. I have a sorted list of the 152
unique numbers in a separate column. I need a VBA routine to list the times
each number is used beside the unique number column. Any help is appreciated.

John

How do I count the instances of numbers in a list?
 

Assume the 152 unique number list is in column D and the original list is in
Column A

Sub Countem()
Dim rng as Range
set rng = range(cells(1,"D"),cells(1,"D").End(xlup))
rng.offset(0,1).Formula = "=countif(A:A,D1)"
rng.offset(0,1).formula = rng.offset(0,1).value
End Sub

--
Regards,
Tom Ogilvy


"John@NGC" wrote:

I have a list of numbers, most duplicates. I have a sorted list of the 152
unique numbers in a separate column. I need a VBA routine to list the times
each number is used beside the unique number column. Any help is appreciated.

John

How do I count the instances of numbers in a list?
 

I have a solution, using arrays! Give this a go....


Code:
--------------------

Dim UniqueArry(), Count%, i%, maxArry%, NewArray(), maxNewArry%

Erase UniqueArry
Erase NewArray

'Fill Array with Unique Numbers
i = 1
Do
maxArry = maxArry + 1
ReDim Preserve UniqueArry(1 To maxArry)
UniqueArry(maxArry) = Cells(i, 2).Value 'Unique Number
i = i + 1
Loop Until Cells(i, 2).Value = ""


'loop non-unques values and count
i = 1
Do
Cells(i, 1).Select

Count = 0
For j = 1 To maxArry

If ActiveCell.Value = UniqueArry(j) Then
Count = Count + 1
End If

Next

maxNewArry = maxNewArry + 1
ReDim Preserve NewArray(1 To 2, 1 To maxNewArry)
NewArray(1, maxNewArry) = UniqueArry(j) 'Unique Number
NewArray(2, maxNewArry) = Count 'Count of the number reoccurance

i = i + 1
Loop Until Cells(i, 1).Value = ""

--------------------


HTH


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=533746

How do I count the instances of numbers in a list?
 

Toms solution may be the better option....less code and easier to
understand!


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=533746

How do I count the instances of numbers in a list?
 

Thanks Tom for the info, however I have to show my ignorance. When I set the
numbered list just as you said, I only get a count of 1 in "E1". (I am
running the macro from "E1" if that makes a difference.) My first number is
101 used 1 times. 103 however is used 3 times but I get nothing in "E3". My
unique list runs from "D1" thru "D158". By the way, I should have said 158
unique numbers in lieu of 152.
Please advise.

"Tom Ogilvy" wrote:

Assume the 152 unique number list is in column D and the original list is in
Column A

Sub Countem()
Dim rng as Range
set rng = range(cells(1,"D"),cells(1,"D").End(xlup))
rng.offset(0,1).Formula = "=countif(A:A,D1)"
rng.offset(0,1).formula = rng.offset(0,1).value
End Sub

--
Regards,
Tom Ogilvy


"John@NGC" wrote:

I have a list of numbers, most duplicates. I have a sorted list of the 152
unique numbers in a separate column. I need a VBA routine to list the times
each number is used beside the unique number column. Any help is appreciated.

John

How do I count the instances of numbers in a list?
 

Sorry, there was a typo:

Sub Countem()
Dim rng as Range
set rng = range(cells(1,"D"),cells(rows.count,"D").End(xlup) )
rng.offset(0,1).Formula = "=countif(A:A,D1)"
rng.offset(0,1).formula = rng.offset(0,1).value
End Sub


or you can just do
Sub countem1()
With Range("E1:E158")
.Formula = "=Countif(A:A,D1)"
.Formula = .Value
End With
end Sub

--
Regards,
Tom Ogilvy



"John@NGC" wrote:

Thanks Tom for the info, however I have to show my ignorance. When I set the
numbered list just as you said, I only get a count of 1 in "E1". (I am
running the macro from "E1" if that makes a difference.) My first number is
101 used 1 times. 103 however is used 3 times but I get nothing in "E3". My
unique list runs from "D1" thru "D158". By the way, I should have said 158
unique numbers in lieu of 152.
Please advise.

"Tom Ogilvy" wrote:

Assume the 152 unique number list is in column D and the original list is in
Column A

Sub Countem()
Dim rng as Range
set rng = range(cells(1,"D"),cells(1,"D").End(xlup))
rng.offset(0,1).Formula = "=countif(A:A,D1)"
rng.offset(0,1).formula = rng.offset(0,1).value
End Sub

--
Regards,
Tom Ogilvy


"John@NGC" wrote:

I have a list of numbers, most duplicates. I have a sorted list of the 152
unique numbers in a separate column. I need a VBA routine to list the times
each number is used beside the unique number column. Any help is appreciated.

John

How do I count the instances of numbers in a list?
 

Thanks Tom, you make it look so easy! I wish I understood how it worked
without even a loop or an offset.
Appreciate your help.

"Tom Ogilvy" wrote:

Sorry, there was a typo:

Sub Countem()
Dim rng as Range
set rng = range(cells(1,"D"),cells(rows.count,"D").End(xlup) )
rng.offset(0,1).Formula = "=countif(A:A,D1)"
rng.offset(0,1).formula = rng.offset(0,1).value
End Sub


or you can just do
Sub countem1()
With Range("E1:E158")
.Formula = "=Countif(A:A,D1)"
.Formula = .Value
End With
end Sub

--
Regards,
Tom Ogilvy



"John@NGC" wrote:

Thanks Tom for the info, however I have to show my ignorance. When I set the
numbered list just as you said, I only get a count of 1 in "E1". (I am
running the macro from "E1" if that makes a difference.) My first number is
101 used 1 times. 103 however is used 3 times but I get nothing in "E3". My
unique list runs from "D1" thru "D158". By the way, I should have said 158
unique numbers in lieu of 152.
Please advise.

"Tom Ogilvy" wrote:

Assume the 152 unique number list is in column D and the original list is in
Column A

Sub Countem()
Dim rng as Range
set rng = range(cells(1,"D"),cells(1,"D").End(xlup))
rng.offset(0,1).Formula = "=countif(A:A,D1)"
rng.offset(0,1).formula = rng.offset(0,1).value
End Sub

--
Regards,
Tom Ogilvy


"John@NGC" wrote:

I have a list of numbers, most duplicates. I have a sorted list of the 152
unique numbers in a separate column. I need a VBA routine to list the times
each number is used beside the unique number column. Any help is appreciated.

John

How do I count the instances of numbers in a list?
 

It just uses the countif formula with proper absolute and relative cell
referencing as if you entered it in the top cell and drag filled it down the
column.

--
Regards,
Tom Ogilvy


"John@NGC" wrote:

Thanks Tom, you make it look so easy! I wish I understood how it worked
without even a loop or an offset.
Appreciate your help.

"Tom Ogilvy" wrote:

Sorry, there was a typo:

Sub Countem()
Dim rng as Range
set rng = range(cells(1,"D"),cells(rows.count,"D").End(xlup) )
rng.offset(0,1).Formula = "=countif(A:A,D1)"
rng.offset(0,1).formula = rng.offset(0,1).value
End Sub


or you can just do
Sub countem1()
With Range("E1:E158")
.Formula = "=Countif(A:A,D1)"
.Formula = .Value
End With
end Sub

--
Regards,
Tom Ogilvy



"John@NGC" wrote:

Thanks Tom for the info, however I have to show my ignorance. When I set the
numbered list just as you said, I only get a count of 1 in "E1". (I am
running the macro from "E1" if that makes a difference.) My first number is
101 used 1 times. 103 however is used 3 times but I get nothing in "E3". My
unique list runs from "D1" thru "D158". By the way, I should have said 158
unique numbers in lieu of 152.
Please advise.

"Tom Ogilvy" wrote:

Assume the 152 unique number list is in column D and the original list is in
Column A

Sub Countem()
Dim rng as Range
set rng = range(cells(1,"D"),cells(1,"D").End(xlup))
rng.offset(0,1).Formula = "=countif(A:A,D1)"
rng.offset(0,1).formula = rng.offset(0,1).value
End Sub

--
Regards,
Tom Ogilvy


"John@NGC" wrote:

I have a list of numbers, most duplicates. I have a sorted list of the 152
unique numbers in a separate column. I need a VBA routine to list the times
each number is used beside the unique number column. Any help is appreciated.

John