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 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
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
I have a solution, using arrays! Give this a go....
HTHDim 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 = ""
Toms solution may be the better option....less code and easier to understand!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks