Count Position of Filtered TEXT cells in a column

Hi All,

I have a column of Firstnames and would like to count their positon in the
column. If a name matches the name directly beneath it, a count of zero
should be returned. If not, a sequential count of the number of Rows away
from the last time it appeared in the list is needed. Column "B" houses a
sequential count of the number of names in the list (including duplicates).
First Row of data starts in Row 11 Column "C" which houses the names.
Results for the returned Count use the same Row, Column "D". The expected
"filtered" Results should count only the visible cells. The first returned
count in Column "D" - filtered or unfiltered must be zero.

Example of Unfiltered Columns:
Column “B” Column “C” Column “D”
RowNo. Sequential Count Names Count
11 1 Sharon 0
12 2 Terry 1
13 3 Robert 2
14 4 Sam 3
15 5 Dave 4
16 6 Sam 1
17 7 Jan 6
18 8 Sam 1
19 9 Jay 8
20 10 Robert 6
21 11 Deborah 10
22 12 Deborah 0
23 13 Sharon 11
24 14 Rita 13
25 15 Jay 5



Expected Filtered Results:
Column “B” Column “C” Column “D”
RowNo. Sequential Count Names Count


13 3 Robert 0
14 4 Sam 1

16 6 Sam 0
17 7 Jan 3
18 8 Sam 1
19 9 Jay 5
20 10 Robert 5
21 11 Deborah 7
22 12 Deborah 0

24 14 Rita 9


Apologies, if columns of data misaligned.

Regards,
Sam

--
Message posted via http://www.officekb.com

Hi Sam!

While I'm not able to offer a solution for filtered data, I can offer
one for non-filtered data. Assuming that Column C contains your
'Names'...

D11, copied down:

=IF(C11=C10,0,IF(COUNTIF($C$11:C11,C11)-1,(ROW()-ROW($C$11))-LARGE(IF($C$
11:C11=C11,ROW($C$11:C11)-ROW($C$11)+1),2),ROW()-ROW($C$11)))

....confirmed with CONTROL+SHIFT+ENTER. As far as filtered data is
concerned, one option might be to copy and paste into another location,
and then use the above formula, adjusting the references accordingly.

Hope this helps!

In article <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> wrote:

> Hi All,
>
> I have a column of Firstnames and would like to count their positon in the
> column. If a name matches the name directly beneath it, a count of zero
> should be returned. If not, a sequential count of the number of Rows away
> from the last time it appeared in the list is needed. Column "B" houses a
> sequential count of the number of names in the list (including duplicates).
> First Row of data starts in Row 11 Column "C" which houses the names.
> Results for the returned Count use the same Row, Column "D". The expected
> "filtered" Results should count only the visible cells. The first returned
> count in Column "D" - filtered or unfiltered must be zero.
>
> Example of Unfiltered Columns:
> Column “B” Column “C” Column “D”
> RowNo. Sequential Count Names Count
> 11 1 Sharon 0
> 12 2 Terry 1
> 13 3 Robert 2
> 14 4 Sam 3
> 15 5 Dave 4
> 16 6 Sam 1
> 17 7 Jan 6
> 18 8 Sam 1
> 19 9 Jay 8
> 20 10 Robert 6
> 21 11 Deborah 10
> 22 12 Deborah 0
> 23 13 Sharon 11
> 24 14 Rita 13
> 25 15 Jay 5
>
>
>
> Expected Filtered Results:
> Column “B” Column “C” Column “D”
> RowNo. Sequential Count Names Count
>
>
> 13 3 Robert 0
> 14 4 Sam 1
>
> 16 6 Sam 0
> 17 7 Jan 3
> 18 8 Sam 1
> 19 9 Jay 5
> 20 10 Robert 5
> 21 11 Deborah 7
> 22 12 Deborah 0
>
> 24 14 Rita 9
>
>
> Apologies, if columns of data misaligned.
>
> Regards,
> Sam

Hi Domenic,

Thank you very much for time and assistance. Appreciate suggestion for
filtered data but it really does need to stay in its original location as
is, when filtered.

Appreciate any further assistance to work with filtered data.


D11, copied down:

=IF(C11=C10,0,IF(COUNTIF($C$11:C11,C11)-1,(ROW()-ROW($C$11))-LARGE(IF($C$
11:C11=C11,ROW($C$11:C11)-ROW($C$11)+1),2),ROW()-ROW($C$11)))

....confirmed with CONTROL+SHIFT+ENTER. As far as filtered data is
concerned, one option might be to copy and paste into another location,
and then use the above formula, adjusting the references accordingly

Thanks
Sam

--
Message posted via http://www.officekb.com

Hi Domenic,

I've taken another look at my scenario and what I'm trying to achieve with
the formula. I think if the cell references could possibly be replaced by
the use of the OFFSET and SUBTOTAL functions, perhaps the Count results
returned would reflect only the visible filtered data - maybe.

My thoughts behind using OFFSET is to negate the need for the current hard
coded cell referencing so that the formula sees the Rows of data in the
Column purely from a position of "Row above" and "Row below" or "Row below"
and "Row above." So that the actual cell Row reference will be immaterial,
if that's possible and the count will provide the correct results for
visible filtered data?

Is the above possible?

Further help appreciated.
------------------------------
While I'm not able to offer a solution for filtered data, I can offer one
for non-filtered data. Assuming that Column C contains your
'Names'...

D11, copied down:

=IF(C11=C10,0,IF(COUNTIF($C$11:C11,C11)-1,(ROW()-ROW($C$11))-LARGE(IF($C$
11:C11=C11,ROW($C$11:C11)-ROW($C$11)+1),2),ROW()-ROW($C$11)))

....confirmed with CONTROL+SHIFT+ENTER.
-------------------------------

Thanks
Sam

--
Message posted via http://www.officekb.com

Hi Sam!

I initially tried to come up with a solution along those lines but was
unsuccessful. I don't know whether it's possible. However, I may be
able to provide you with another option.

Instead of filtering the data using Excel's built-in filter, you may be
able to do so using Aladin's formula system. Then, you could have my
formula applied against this filtered data.

So, basically, here's how it would work. In another part of your
worksheet or a separate sheet, you would input your criterion or
criteria, a filtered list would be generated, and my formula would
automatically return the desired results.

If you're interested in this approach, please provide me with the
criteria involved in filtering your data and I'll see what I can do.

Hope this helps!

In article <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> wrote:

> Hi Domenic,
>
> I've taken another look at my scenario and what I'm trying to achieve with
> the formula. I think if the cell references could possibly be replaced by
> the use of the OFFSET and SUBTOTAL functions, perhaps the Count results
> returned would reflect only the visible filtered data - maybe.
>
> My thoughts behind using OFFSET is to negate the need for the current hard
> coded cell referencing so that the formula sees the Rows of data in the
> Column purely from a position of "Row above" and "Row below" or "Row below"
> and "Row above." So that the actual cell Row reference will be immaterial,
> if that's possible and the count will provide the correct results for
> visible filtered data?
>
> Is the above possible?

Hi Domenic,

Thanks for reply. Just wanted to clarify: my criterion/criteria is based on
text or numeric values from any range of columns that have numerous unique
text and numeric values; so the criteria varies and changes - it isn't
really any one particular criterion and that's why the AutoFilter lends
itself so well to the on the fly filtering.

However, for example, I may filter on age.

I am not familiar with Aladin's Formula system - will this be a problem?


Thanks
Sam

--
Message posted via http://www.officekb.com

Actually, since your criteria will vary in number and range of columns,
and you no doubt want to keep that flexibility, you can continue to use
AutoFilter to filter your data and use the following formula system to
help return the desired results...

Assumptions:

1) Sheet1 contains your source data

2) A10:D10 contains your headers/labels

3) A11:D25 contains your data

4) Column C contains your 'Names'

Formulas:

On Sheet2....

A1: enter a 0 (zero)

A2, copied down:

=IF(SUBTOTAL(3,Sheet1!A11),LOOKUP(9.99999999999999E+307,$A$1:A1)+1,"")

B1:

=LOOKUP(9.99999999999999E+307,A:A)

C2, copied down:

=IF(ROW()-ROW($C$2)+1<=$B$1,MATCH(ROW()-ROW($C$2)+1,$A$2:$A$16,0),"")

D2, copied down:

=IF(N(C2),INDEX(Sheet1!$C$11:$C$25,C2),"")

E2, copied down:

=IF(N(C2),IF(D2=D1,0,IF(COUNTIF($D$2:D2,D2)-1,(ROW()-ROW($D$2))-LARGE(IF(
$D$2:D2=D2,ROW($D$2:D2)-ROW($D$2)+1),2),ROW()-ROW($D$2))),"")

Now, all you have to do is filter your data using Excel's AutoFilter and
the desired results will automatically be returned on Sheet2.

Hope this helps!

In article <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> wrote:

> Hi Domenic,
>
> Thanks for reply. Just wanted to clarify: my criterion/criteria is based on
> text or numeric values from any range of columns that have numerous unique
> text and numeric values; so the criteria varies and changes - it isn't
> really any one particular criterion and that's why the AutoFilter lends
> itself so well to the on the fly filtering.
>
> However, for example, I may filter on age.
>
> I am not familiar with Aladin's Formula system - will this be a problem?
>
>
> Thanks
> Sam

I forgot to mention that the formula for E2 needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER.

In article <[email protected]>,
Domenic <[email protected]> wrote:

> Actually, since your criteria will vary in number and range of columns,
> and you no doubt want to keep that flexibility, you can continue to use
> AutoFilter to filter your data and use the following formula system to
> help return the desired results...
>
> Assumptions:
>
> 1) Sheet1 contains your source data
>
> 2) A10:D10 contains your headers/labels
>
> 3) A11:D25 contains your data
>
> 4) Column C contains your 'Names'
>
> Formulas:
>
> On Sheet2....
>
> A1: enter a 0 (zero)
>
> A2, copied down:
>
> =IF(SUBTOTAL(3,Sheet1!A11),LOOKUP(9.99999999999999E+307,$A$1:A1)+1,"")
>
> B1:
>
> =LOOKUP(9.99999999999999E+307,A:A)
>
> C2, copied down:
>
> =IF(ROW()-ROW($C$2)+1<=$B$1,MATCH(ROW()-ROW($C$2)+1,$A$2:$A$16,0),"")
>
> D2, copied down:
>
> =IF(N(C2),INDEX(Sheet1!$C$11:$C$25,C2),"")
>
> E2, copied down:
>
> =IF(N(C2),IF(D2=D1,0,IF(COUNTIF($D$2:D2,D2)-1,(ROW()-ROW($D$2))-LARGE(IF(
> $D$2:D2=D2,ROW($D$2:D2)-ROW($D$2)+1),2),ROW()-ROW($D$2))),"")
>
> Now, all you have to do is filter your data using Excel's AutoFilter and
> the desired results will automatically be returned on Sheet2.
>
> Hope this helps!
>
> In article <[email protected]>,
> "Sam via OfficeKB.com" <[email protected]> wrote:
>
> > Hi Domenic,
> >
> > Thanks for reply. Just wanted to clarify: my criterion/criteria is based on
> > text or numeric values from any range of columns that have numerous unique
> > text and numeric values; so the criteria varies and changes - it isn't
> > really any one particular criterion and that's why the AutoFilter lends
> > itself so well to the on the fly filtering.
> >
> > However, for example, I may filter on age.
> >
> > I am not familiar with Aladin's Formula system - will this be a problem?
> >
> >
> > Thanks
> > Sam

Hi Domenic,

Thank you very much for your time and assistance. Your suggested solution
does provide the required results - very much appreciated.

Thanks,
Sam

--
Message posted via http://www.officekb.com