View Single Post
#2
May 16th 05, 08:40 PM
 Domenic Posts: n/a

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