Can an Array formula count unique values and report no. of tim
Minor amendment ... sorry.
Sub GetUniqueNumbers()
Dim iLastRow As Long, NextRow As Long, NextNum As Long
Dim rngB As Range
Dim V As Variant
Set rngB = Range("B1")
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
V = Range("A1:A" & iLastRow + 1)
NextRow = 1
NextNum = V(NextRow, 1)
numcount = NextRow
Do
numcount = NextRow
Do While V(NextRow, 1) = V(NextRow + 1, 1)
NextRow = NextRow + 1
Loop
numcount = NextRow - numcount + 1
rngB = NextNum
rngB.Offset(0, 1) = numcount
Set rngB = rngB.Offset(1, 0)
NextRow = NextRow + 1
NextNum = V(NextRow, 1)
numcount = NextRow
Loop While NextRow < iLastRow
If NextNum < 0 Then
rngB = NextNum
rngB.Offset(0, 1) = NextRow - numcount + 1
End If
End Sub
"Toppers" wrote:
Hi,
Try this: assumes data in column A is ascending order.
Sub GetUniqueNumbers()
Dim iLastRow As Long, NextRow As Long, NextNum As Long
Dim rngB As Range
Dim V As Variant
Set rngB = Range("B1")
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
V = Range("A1:A" & iLastRow + 1)
NextRow = 1
NextNum = V(NextRow, 1)
Do
numcount = NextRow
Do While V(NextRow, 1) = V(NextRow + 1, 1)
NextRow = NextRow + 1
Loop
numcount = NextRow - numcount + 1
rngB = NextNum
rngB.Offset(0, 1) = numcount
Set rngB = rngB.Offset(1, 0)
NextRow = NextRow + 1
NextNum = V(NextRow, 1)
Loop While NextRow < iLastRow
If NextNum < 0 Then
rngB = NextNum
rngB.Offset(0, 1) = numcount
End If
End Sub
HTH
"Father Guido" wrote:
Hi,
Is it possible to use an array formula on a range and have it report
the unique values in that range and the number of times each was
encountered?
Barring an array formula solution (preferred) I would entertain a
macro solution.
Thanks,
Norm
XL2002
Windows2000
Eaxample: To get the unique entries in Col A and report then in Col B
and the number of times for each in Col C.
Col A Col B Col C
1 1 1
3 3 1
7 7 1
9 9 3
9 12 1
9 16 1
12 21 2
16 33 1
21 44 1
21 78 7
33 81 1
44 132 1
78 156 1
78
78
78
78
78
78
81
132
156
|