Filtering filtered data

tanda

New Member
Joined
Feb 18, 2002
Messages
13
After autofiltering data and while the autofilter is open, is there any way to refilter the data. For example, after filtering 100 rows down to 5 rows, I would like to find the second highest of the five (not the second highest of the original 100).

I know it can be done by copying and pasting the filtered data to another sheet and then running the large function to find the second largest. I would prefer a simpler and more elegant way to do this. The result of this operation will be assigned to a variable and then no further use is needed for the filtered data.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
You can filter in cascade as many time as you wish on the same data set. The new custom conditions are going to be applied on the filtered data set.
 
Upvote 0
Hi tanda:
To elaborate what Corni mentioned, following is a step by step approach to get the TOP values from filtered dataset
1) let us say I have my original data in cells G7 through G12

original data
21
70
80
95
67
43

2) I want to filter this data with a custom filter applied so that rows with >=43 are selected ... my filtered data is shown below
70
80
95
67
43

3) now I want to pick up the TOP three values from this filtered selection -- my selection is hown below
70
80
95

4) I can even go a step further and sort this select list, say in descending order to see my list from largest of the three, then the next largest, and so on -- my sorted TOP three list is shown below
95
80
70

I apologize if it is too much detail for you.
HTH
Please post back if it works for you ... otherwise explain a little further and let us take it from there!
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top