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....


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

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

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