Count of unique values in a filtered column using 'find'

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am still trying to get a count of unique values in a filtered column

The formula which someone posted does not work. (I get a #NAME error) from the following formula

=COUNTDIFF(IF(SUBTOTAL(3,OFFSET(A6,ROW(A6:A10)-MIN(ROW(A6:A10)),,1)),A6:A10))-(COUNTA(A6:A10)<>SUBTOTAL(3,A6:A10)

I tried writing a function in VB as follows using the 'find' method

Function CtUnqFiltered(rng As Range) As Lon
Dim fc As Excel.Rang
Dim ct As Lon
ct =
For Each c In rng.Cell
If (Not rng.Rows(c.Row - rng.Rows.Count).Hidden) And (Not IsEmpty(c)) The
Set fc = rng.Find(what:=c.Value, after:=c
If (fc Is Nothing) Then ct = ct +
End I
Nex
CtUnqFiltered = c
End Functio

This function should work, only counting the last of a unique value in the list, using the 'Find' The problem is that the 'Find' always returns nothing(never finds the value), even if there is another of the same value later in the range. Am i doing something wrong with the 'find'? The doc says it should return a the cell where it finds the value

TIA
DOTJake
 
..Jake,

As an alternative to the multiple search function (which did not work for
me), try using a custom collection as follows:

Function CtUnqFiltered(rng As Range) As Long
Dim fc As Excel.Range
Dim xcUnique As New Collection
Dim ct As Long
ct = 0
On Error Resume Next
For Each c In rng.Cells
If (Not c.EntireRow.Hidden) And (Not IsEmpty(c)) Then
xcUnique.Add CStr(c.Value), CStr(c.Value)
End If
Next
CtUnqFiltered = xcUnique.Count
End Function

Each element in the collection must have a unique "key" parameter. If the
key is not unique, an error is raised, which the resume next clause allows
us to skip.

BTW, I changed your check for hidden row. I think this is simpler and works
fine.

Hope this solves your current dilemma,

Alex J

DOTJake said:
I am still trying to get a count of unique values in a filtered column.

The formula which someone posted does not work. (I get a #NAME error) from the following formula:
Click to expand...
=COUNTDIFF(IF(SUBTOTAL(3,OFFSET(A6,ROW(A6:A10)-MIN(ROW(A6:A10)),,1)),A6:A10)
)-(COUNTA(A6:A10) said:
I tried writing a function in VB as follows using the 'find' method:

Function CtUnqFiltered(rng As Range) As Long
Dim fc As Excel.Range
Dim ct As Long
ct = 0
For Each c In rng.Cells
If (Not rng.Rows(c.Row - rng.Rows.Count).Hidden) And (Not IsEmpty(c)) Then
Set fc = rng.Find(what:=c.Value, after:=c)
If (fc Is Nothing) Then ct = ct + 1
End If
Next
CtUnqFiltered = ct
End Function

This function should work, only counting the last of a unique value in the
Click to expand...
list, using the 'Find' The problem is that the 'Find' always returns
nothing(never finds the value), even if there is another of the same value
later in the range. Am i doing something wrong with the 'find'? The doc
says it should return a the cell where it finds the value.
 
Thanks Alex,

I had already done the collection thing but had had wanted to know why the 'Find' did not work 'as advertized'.

Thanks for the better 'hidden' check.

DOTJake
 
COUNTDIFF is a function from an add-called morefunc.xll, dowloadable from:

http://longre.free.fr/english/index.html

DOTJake said:
I am still trying to get a count of unique values in a filtered column.

The formula which someone posted does not work. (I get a #NAME error) from the following formula:
Click to expand...
=COUNTDIFF(IF(SUBTOTAL(3,OFFSET(A6,ROW(A6:A10)-MIN(ROW(A6:A10)),,1)),A6:A10)
)-(COUNTA(A6:A10) said:
I tried writing a function in VB as follows using the 'find' method:

Function CtUnqFiltered(rng As Range) As Long
Dim fc As Excel.Range
Dim ct As Long
ct = 0
For Each c In rng.Cells
If (Not rng.Rows(c.Row - rng.Rows.Count).Hidden) And (Not IsEmpty(c)) Then
Set fc = rng.Find(what:=c.Value, after:=c)
If (fc Is Nothing) Then ct = ct + 1
End If
Next
CtUnqFiltered = ct
End Function

This function should work, only counting the last of a unique value in the
Click to expand...
list, using the 'Find' The problem is that the 'Find' always returns
nothing(never finds the value), even if there is another of the same value
later in the range. Am i doing something wrong with the 'find'? The doc
says it should return a the cell where it finds the value.
 
..find doesn't work when called from a worksheet function in some versions of
excel.

It does work in xl2002, though.
 
Back
Top