Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can an Array formula count unique values and report no. of times for each?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can an Array formula count unique values and report no. of times f
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unique values in an array. | Excel Discussion (Misc queries) | |||
i want to count how many times an entry occurs in an array | Excel Worksheet Functions | |||
Array formula for unique values | Excel Worksheet Functions | |||
Array formula for unique values | Excel Worksheet Functions | |||
Count unique values and create list based on these values | Excel Worksheet Functions |