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

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

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

Please Login or Register  to view this content.

HTH

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

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

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

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

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