copying exact position in sheet that have filtered data

[denis2201]

Hi,

I need to solve something that look to me like easy thing, but so far after 5 hours of google and youtube searching i still can't find solution.
Here is the thing. I have table with lot of filtered data, but the solution i need can be found inside single column...

Lets say i have base of thousand companies and they start from field D4. In D3 we have filter "company name". What i want is to copy into cell D1 name of company that i have in D4 after i use filter. So i need copy position cell, 4th cell on the table, no matter which row it is after filter.
I hope you do understand my problem. I am not power user, but i know how to use things once i see examples, so i learnt to use subtotal, offset and such things, but problem here is that i need to copy, hm, text.

thx in advance, d

[jason.b75]

Using an extra column, assuming column E is empty (otherwise, use an available column, changing any references to column E in the formulas to the column that you use).

In E4, enter the formula,

=SUBTOTAL(3,D4)

And use autofill to copy it down to the end of your table.

Then in D1, enter the formula

=INDEX(D:D,MATCH(1,E:E,0))

Does that do what you need?

[denis2201]

yet not sure why this work, but it works. Thx a lot jason

[denis2201]

Using an extra column, assuming column E is empty (otherwise, use an available column, changing any references to column E in the formulas to the column that you use).

In E4, enter the formula,

=SUBTOTAL(3,D4)

And use autofill to copy it down to the end of your table.

Then in D1, enter the formula

=INDEX(D:D,MATCH(1,E:E,0))

Does that do what you need?

After all examination this formula seems to be a bit better for one reason.. The one that Tony gave takes enormous resources for calculation, while this one does it instantly... But as always, there is a but...
why this formula always leave in filter last row, no mattter that is different company in my particular case? Is it avoidable?

[denis2201]

Using an extra column, assuming column E is empty (otherwise, use an available column, changing any references to column E in the formulas to the column that you use).

In E4, enter the formula,

=SUBTOTAL(3,D4)

And use autofill to copy it down to the end of your table.

Then in D1, enter the formula

=INDEX(D:D,MATCH(1,E:E,0))

Does that do what you need?

After all examination this formula seems to be a bit better for one reason.. The one that Tony gave takes enormous resources for calculation, while this one does it instantly... But as always, there is a but...
why this formula always leave in filter last row, no mattter that is different company in my particular case? Is it avoidable?

[Tony Valko]

If I understand what you want this formula will return the 4th entry from a filtered list.

=INDEX(D4:D100,MATCH(4,SUBTOTAL(3,OFFSET(D4:D100,,,ROW(D4:D100)-ROW(D4)+1)),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[denis2201]

nope, i need 1st one data after applying filter. in my table it is D4. so i need data that is in d column, on 4th position from top. it doesnt matter if it is row178, i just need visually 4th place. and i need to place result in D1 field.. latter i will use vlookup for that D1, but that doesnt matter at this post

[jason.b75]

Try Tony's formula, changing the lookup value of match from 4 to 1, i.e.

=INDEX(D4:D100,MATCH(1,SUBTOTAL(3,OFFSET(D4:D100,,,ROW(D4:D100)-ROW(D4)+1)),0))

Does that work?

[denis2201]

Try Tony's formula, changing the lookup value of match from 4 to 1, i.e.

=INDEX(D4:D100,MATCH(1,SUBTOTAL(3,OFFSET(D4:D100,,,ROW(D4:D100)-ROW(D4)+1)),0))

Does that work?

Nope, this doesn't work either. But like i said, i am satisfied with extra column solution.

[jason.b75]

Subtotal ignores rows hidden by a filter, so in both methods, the hidden rows will return a zero value to the subtotal function, match looks for the first row with a subtotal value of 1 (first visible row).

Tony's method is more versatile than mine, in that it could return any visible row simply by changing the lookup value.

edit:-

I ran a test on Tony's method and it appeared to work as expected once the value was changed to 1.

Filtered data so that rows 4:15 were hidden and formula returned data from D16.

[Tony Valko]

[denis2201]

sorry, although u politely write me rule for array formulas, i forgot to press shift ctrl. formula works perfectly after small change that jason pointed out.

[Tony Valko]

Good deal. Thanks for the feedback!

[jason.b75]

I've used subtotal with filtered rows for similar purposes before, but not noticed that happen until now, maybe a problem that only occurs with newer versions of excel.

I tried a different function which appears to work correctly, try this in place of subtotal.

=AGGREGATE(3,5,D4)

[denis2201]

I've used subtotal with filtered rows for similar purposes before, but not noticed that happen until now, maybe a problem that only occurs with newer versions of excel.

I tried a different function which appears to work correctly, try this in place of subtotal.

=AGGREGATE(3,5,D4)

if u ever come to croatia, feel free to contact me, i ll be your guide for free, like you guide me through excel.
cheers,

d