Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default 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
  #2   Report Post  
Domenic
 
Posts: n/a
Default

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 ,
"Sam via OfficeKB.com" 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

  #3   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default

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
  #4   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default

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
  #5   Report Post  
Domenic
 
Posts: n/a
Default

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 ,
"Sam via OfficeKB.com" 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?



  #6   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default

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
  #7   Report Post  
Domenic
 
Posts: n/a
Default

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.99999999999999 E+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 ,
"Sam via OfficeKB.com" 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

  #8   Report Post  
Domenic
 
Posts: n/a
Default

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

In article ,
Domenic 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.99999999999999 E+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 ,
"Sam via OfficeKB.com" 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

  #9   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count cells in a column that contain dates Cachod1 New Users to Excel 1 March 29th 05 08:56 PM
In a column of text data, how do I delete random cells that have . Minivann Excel Discussion (Misc queries) 2 January 26th 05 10:07 PM
Putting text in a column based on variable text from another colum Jacky D. Excel Discussion (Misc queries) 1 December 16th 04 07:09 PM
I need to count the occurence of text in a range of cells (which . Brenda Excel Worksheet Functions 1 December 12th 04 10:00 PM
how do I 'count' the number of cells with a text in red or black? Anjin Topeng Excel Worksheet Functions 2 November 4th 04 07:42 AM


All times are GMT +1. The time now is 10:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"