Find "nth" input, whilst using a filter

[ellison]

Hi, we are trying to find a way to find the "nth" input when our data (in Col A) has got a filter on it.

i.e. find a quick way to accurately find the correct item in the list (in order for the updated info to be manually included in Column D after checking it against external sources) .

I've been told that hitting Ctrl+F is slowing people down too much as the entries (in Col A) are often very long (grr!), so they've asked if we could use a helper column to find the "nth" input...

So far, we've got a helper column (Col B) and an "nth counter" (Col C) but it's pretty clunky and depends on people updating the Col B helper column to reflect that a new filter has been put on.

Book1
	A	B	C	D	E
1	Input	Nth-helper	Nth	Updated Info	This would be the nth based on selecting all of the available inputs
2	Alpha	Y	1
3	Beta	Y	2
4	Charlie	Y	3
5		Y	4
6	Delta	Y	5
7	Echo	Y	6
8	Foxtrot	Y	7
9	1	Y	8
10	2	Y	9
11	3	Y	10
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=COUNTIF(B$2:B2,B2)

And here's the alternative where we've made a filter for inputs in Col A that "contain A"

Book1
	A	B	C	D	E
1	Input	Nth-helper	Nth	Updated Info	This would be the nth based on selecting Input which contain "A"
2	Alpha	containsA	1
3	Beta	containsA	2
4	Charlie	containsA	3
6	Delta	containsA	4
Sheet1

Cell Formulas
Range	Formula
C2:C4,C6	C2	=COUNTIF(B$2:B2,B2)

I'm wondering if there is a better way to deal with this

Is there maybe a way that the nth counter could reflect the data filter which has been added?

Added issue: the lists are literally thousands of rows long & sometimes contain blanks, alpha, numeric or alphanumeric entries


Any help much appreciated, huge thanks for taking a look!

 

[JamesCanale]

Not sure that I follow. I made a filterable table where the users might filter for any old combination of things. You want to see the running count of the filtered list.

MrExcelPlayground20.xlsx
	A	B	C	D
1	Input	Other thing	Nth	Updated Info
2	Alpha	A	1
3	Beta	A	2
4	Charlie	B	3
5	Fred	A	4
6	Delta	B	5
Sheet27

Cell Formulas
Range	Formula
C2:C6	C2	=SUBTOTAL(103,A$2:A2)

When this is filtered, the column C value recalculates based on the 'visible' subtotal of counta.

 

[ellison]

absolutely brilliant - and huge thanks for understanding my waffling!!!

"running count of the filtered list",superb