Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count of unique values in a filtered column using 'find'
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count of unique values in a filtered column using 'find'
..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" wrote in message ... 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 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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count of unique values in a filtered column using 'find'
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count of unique values in a filtered column using 'find'
COUNTDIFF is a function from an add-called morefunc.xll, dowloadable from:
http://longre.free.fr/english/index.html "DOTJake" wrote in message ... 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 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 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count of unique values in a filtered column using 'find'
..find doesn't work when called from a worksheet function in some versions of
excel. It does work in xl2002, though. DOTJake wrote: 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 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 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Unique Values but not Filtered or Hidden Values | Excel Worksheet Functions | |||
How to count the number of unique entries in a filtered column | Excel Discussion (Misc queries) | |||
count unique values in a filtered range | Excel Worksheet Functions | |||
Count Unique Values In A Filtered Row with Duplicates | Excel Discussion (Misc queries) | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions |