Home 
Search 
Today's Posts 
#1




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




Hi Sam!
While I'm not able to offer a solution for filtered data, I can offer one for nonfiltered 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




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




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 nonfiltered 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




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 builtin 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




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




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) A1010 contains your headers/labels 3) A1125 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$22,D2)1,(ROW()ROW($D$2))LARGE(IF( $D$22=D2,ROW($D$22)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




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) A1010 contains your headers/labels 3) A1125 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$22,D2)1,(ROW()ROW($D$2))LARGE(IF( $D$22=D2,ROW($D$22)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




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 
Display Modes  


Similar Threads  
Thread  Forum  
Count cells in a column that contain dates  New Users to Excel  
In a column of text data, how do I delete random cells that have .  Excel Discussion (Misc queries)  
Putting text in a column based on variable text from another colum  Excel Discussion (Misc queries)  
I need to count the occurence of text in a range of cells (which .  Excel Worksheet Functions  
how do I 'count' the number of cells with a text in red or black?  Excel Worksheet Functions 