Filter Top 10 in already filtered data

I am attaching file named Filter top ten records.xlsx in which for example if I filter the item 801 in column L and then want to filter column K amounts, lets assume top 10 amount how can I achieve this. Because when I try in column K number filters > top 10 it filters whole range and shows fewer results for item 801.

Originally Posted by Glenn Kennedy

And the solution at Post 2??

Yes, It's. But I fear about adding extra column and then need to exclude that area from printing area. So It would be more convenient if you can provide the filter solution.

Use Glenn's formula as a conditional formatting formula in K column and set it with fill color, then filter using Filter by Color.

@Glenn Kennedy
Thank You for the effective and quick solution as always.

@josephteh
Thanks for the valuable suggestion.

Originally Posted by Glenn Kennedy

That's a good idea... especially wrapped in NOT

=NOT(AND($L2=801,$K2>=LARGE(IF($L$2:$L$81=801,$K$2:$K$81),10)))

then filter the cells with no fill.

Formula provided in post #2 and #10 works perfectly in conditional formatting with my MS Excel 2007 of Home computer and Workplace computer but When I close the worksheet and reopen, it does not act like it should be. To got the results as earlier, I have to clear previous conditional formatting rule and then re-enter it again to got the expected result. Please suggest the solution.

Originally Posted by Glenn Kennedy

I may have (I did...) attach the wrong file. Try this one...

Sir,
Kindly look into the issue.

Sorry Sir,
I have used both formula provided by you in conditional formatting but issue mentioned in post #13 still remains.

And Yes, Calculation option in my excel is automatic, so there's no issue there.

I feel Conditional formatting is the best option for my query, Kindly solve the issue.

I have tried with all files provided by you including last one, issue remains same with all.

Either I have to clear the previous rule and reapply it again or I have to right click on any cell which contains conditional formatting then > manage rule > edit rule > then ok and apply again but this is cumbersome process

Please someone provide the solution of the query

Originally Posted by josephteh

Use Glenn's formula as a conditional formatting formula in K column and set it with fill color, then filter using Filter by Color.

Can you please provide the solution

Please attach your problem workbook.

Issue remains same as mentioned in post #13, #21 with all files whether it is my original first attachment file post #1 or all files provided as solution here.

Your attached workbook seems to be working well as you did manage to filter the top 10 colored cell. What's the issue here?

Same as Glenn, I have no Excel 2007 and am unable to see your problem.

That's the problem that these workbooks works fine with never version of office but not working as it should be in my office 2007.
So it would be really helpful if someone can provide solution

Is Excel 2007 able to filter by color?

Only first time when I open the file. After closing the file and after that when I reopen file it doesn't show colours because conditional formatting don't work at that time. I have to use the method explain in post #21

Originally Posted by josephteh

Is Excel 2007 able to filter by color?

In general Yes with all other files it does. I request you to please read all posts of this thread, so you can understand the problem clearly.
But I think the provided formula may be heavy for excel 2007

I am already using different Conditional formatting in many other workbooks and It works fine with them. Reinstalling may not be needed since this issue is not with my home computer only but same issue is with my workplace pc also.

This works but I am attaching the sheet which is same as the sheet with whom I have to work. Here I have to also mention that in original sheet there are around 2K to 12K records with whom I have to work with.
I am attaching the original sheet in which I have to filter the top N number of data. There are two possible scenarios with whom I have to work with.
(1) filtering data of column N and then filter top N number of records in column K. for example in column N we filter 77701 and then want to filter top 15 number of records in column K.
(2) filtering data of column N and column G and then filter top N number of records in column K. for example in column N we filter 77701 and in column G filter B1 and then want to filter top 15 number of records in column K.

It works really well,
But I have some doubts
1. is it mandatory to add extra two rows at top?. Isn’t it possible to put the formula in first row of my original file, without occupying extra column or row. I mean it will be really helpful if it can be done within the range of original files cell A1 to cell P!.
2. do advance filter works only if criteria range contains heading else not.

Originally Posted by rorya

You have to have header cells, so you can't have the criteria in the first row. You could put them to the right of your data set.

Is it possible to use the criteria in another sheet ?

Originally Posted by rorya

Yes, you can do that.

I have tried but not working, Can you please help with it.

Okay, Thanks