Count Unique Records in Filtered List
 

I was trying to work with the below function, but it is too slow:
=SUMPRODUCT((C5:C15000<"")/(COUNTIF(C5:C15000,C5:C15000&"")))

Subtotal(103,C5:C15000)
Works alright, but doesn't handle uniques...and it is slow too

Next, I tried this code, and it is certainly fast:
Sub Sub1() ' in sheet module
Dim sCell$, iErr&, iRowV&, iCount&
Dim CollPtr1 As Collection ' pointer to object
Set CollPtr1 = New Collection ' object
For iRowV = 5 To 15000
sCell = Cells(iRowV, 3).Value
If sCell < "" Then ' ck blank
On Error Resume Next ' don't break
CollPtr1.Add "", sCell
iErr = Err.Number
On Error GoTo 0 ' restore error processing
If iErr = 0 Then iCount = iCount + 1
End If
Next iRowV
Cells(3, 3) = iCount
End Sub

However, it doesn't count unique records in a filtered list. I've seen a
Pivot Table count uniques, when the Pivot Table has a helper column.
http://www.contextures.com/xlPivot07.html#Unique

Is this the best way to go? Code, or a pivot table; In think functions will
be too slow (I need to do this for four columns). I'm looking for the best
solution, in terms of speed/performance.

Thanks,
Ryan---


--
RyGuy

Count Unique Records in Filtered List
 

Perhaps in place of:
If sCell < "" Then
we use:
If sCell < "" And Cells(iRowV, 3).Rows.Hidden=False Then


This will force the code to ignore the items that the filter has hidden.
--
Gary''s Student - gsnu200814


"ryguy7272" wrote:

I was trying to work with the below function, but it is too slow:
=SUMPRODUCT((C5:C15000<"")/(COUNTIF(C5:C15000,C5:C15000&"")))

Subtotal(103,C5:C15000)
Works alright, but doesn't handle uniques...and it is slow too

Next, I tried this code, and it is certainly fast:
Sub Sub1() ' in sheet module
Dim sCell$, iErr&, iRowV&, iCount&
Dim CollPtr1 As Collection ' pointer to object
Set CollPtr1 = New Collection ' object
For iRowV = 5 To 15000
sCell = Cells(iRowV, 3).Value
If sCell < "" Then ' ck blank
On Error Resume Next ' don't break
CollPtr1.Add "", sCell
iErr = Err.Number
On Error GoTo 0 ' restore error processing
If iErr = 0 Then iCount = iCount + 1
End If
Next iRowV
Cells(3, 3) = iCount
End Sub

However, it doesn't count unique records in a filtered list. I've seen a
Pivot Table count uniques, when the Pivot Table has a helper column.
http://www.contextures.com/xlPivot07.html#Unique

Is this the best way to go? Code, or a pivot table; In think functions will
be too slow (I need to do this for four columns). I'm looking for the best
solution, in terms of speed/performance.

Thanks,
Ryan---


--
RyGuy

Count Unique Records in Filtered List
 

Unreal!! Perfect, too!!
Thanks GS!!!
Ryan---

--
RyGuy


"Gary''s Student" wrote:

Perhaps in place of:
If sCell < "" Then
we use:
If sCell < "" And Cells(iRowV, 3).Rows.Hidden=False Then


This will force the code to ignore the items that the filter has hidden.
--
Gary''s Student - gsnu200814


"ryguy7272" wrote:

I was trying to work with the below function, but it is too slow:
=SUMPRODUCT((C5:C15000<"")/(COUNTIF(C5:C15000,C5:C15000&"")))

Subtotal(103,C5:C15000)
Works alright, but doesn't handle uniques...and it is slow too

Next, I tried this code, and it is certainly fast:
Sub Sub1() ' in sheet module
Dim sCell$, iErr&, iRowV&, iCount&
Dim CollPtr1 As Collection ' pointer to object
Set CollPtr1 = New Collection ' object
For iRowV = 5 To 15000
sCell = Cells(iRowV, 3).Value
If sCell < "" Then ' ck blank
On Error Resume Next ' don't break
CollPtr1.Add "", sCell
iErr = Err.Number
On Error GoTo 0 ' restore error processing
If iErr = 0 Then iCount = iCount + 1
End If
Next iRowV
Cells(3, 3) = iCount
End Sub

However, it doesn't count unique records in a filtered list. I've seen a
Pivot Table count uniques, when the Pivot Table has a helper column.
http://www.contextures.com/xlPivot07.html#Unique

Is this the best way to go? Code, or a pivot table; In think functions will
be too slow (I need to do this for four columns). I'm looking for the best
solution, in terms of speed/performance.

Thanks,
Ryan---


--
RyGuy