Forum Discussion
Filter data - then filter the filtered data
I have some data that is over 3200 lines with multiple columns each
I know I can just select the headers and put a filter in each column - and can drill down the data
But - I want to create a query page - where I can reference the data (on a different page) - and filter it
That part is easy - and I've done that - but I want to be able to add more filter options to the already filtered data......independently of each other
I know that I can use the formula
=FILTER(Data,(Application=B4)*(Type=C4)*(Series=D4)*(Size=E4)*(Geometry=F4),"SELECT APPLICATION")
But - this will only show the data once all the criteria is met
I want to be able drill down the data using 1 or more criteria in any order
I hope I am explaining that correctly
Can excel do that?
See attached sample file where a tab named List is hidden. Lists on that sheet are dynamically adjusted according the filter(s)/criteria already applied on the Query tab:
With Data formatted as a structured Table, FILTER formula on the Query tab is:
=LET( Filtered, FILTER(Data, IF(ISBLANK(B4), 1, Data[Application] = B4) * IF(ISBLANK(C4), 1, Data[Type] = C4) * IF(ISBLANK(D4), 1, Data[Series] = D4) * IF(ISBLANK(E4), 1, Data[Size] = E4) * IF(ISBLANK(F4), 1, Data[Geometry] = F4) ), IF(QueryCriterias, Filtered, "Select at least a criterion") )Where QueryCriterias in defined in Name Manager as: =COUNTA(Query!$B$4:$F$4)
You've had a fair number of views (approaching 40) without any responses yet. My suspicion is that you leave your readers somewhat puzzled as to how to help. Let's see if I can explain why that might be.
- I have some data that is over 3200 lines with multiple columns each
- I know I can just select the headers and put a filter in each column - and can drill down the data
- But - I want to create a query page - where I can reference the data (on a different page) - and filter it
- I know that I can use the formula
- =FILTER(Data,(Application=B4)*(Type=C4)*(Series=D4)*(Size=E4)*(Geometry=F4),"SELECT APPLICATION")
- But - this will only show the data once all the criteria is met
- I want to be able drill down the data using 1 or more criteria in any order
For whatever it's worth, 3200 lines isn't really a lot. There are databases far more extensive than that. And you can always nest a FILTER within a FILTER function to do secondary or tertiary filtering on data that's gone through a primary filter. Presumably you already know that.
You make a point that you already know you could just sort via the tool bar filter tool, the little diamonds in the header row of your basic dataset, and filter away to your heart's content. But apparently your heart isn't content with that--you "want to create a query page" for your filtering. You have said that quite cleary; what you haven't really done is explain WHY you are so intent on that separate"dashboard" page, and how much flexibility you'd need and why the basic sort right there at the top of the 3200 rows isn't satisfactory. After all, that method really does work quite well, offering a lot of flexibility.
The dynamic array functions, including FILTER, are also wonderful tools, especially given the way that they can work in concert with one another, but it sounds like you want a degree of flexibility that may require writing ad hoc formulas, FILTER within FILTER, or the like.
So let me invite you to explain a bit more fully why the tool bar filter tool is not acceptable, and then the specifics of the flexibility that you apparently need but only allude to.
- True, 3200 lines is not a lot - and I never said it was - I was only stating that is what I am working with - and that it is too much data to scroll through
I want a query page to make it clean and easy to view the data using pre-determined filters - as this document will be given to Sales people and distributors to use as a reference - so I am going to hide the original data tab....and other tabs - so they cannot be messed with
Basically this is a products data file - so I have made the filters change based on the type of information the user is looking for
It is my hope that I can use the first filter - and have the data populate
Then if I wish to use the second filter - it will then filter the already filtered data further......and so on if I wish to use any of the other filters
That way I can see as much or as little of the data as I want
As it stands right now - the data will only populate once I have met all the criteria in the formula
Hopefully that helps answer your questions
I have never used the IFS function.....can it be used in the filter equation?See attached sample file where a tab named List is hidden. Lists on that sheet are dynamically adjusted according the filter(s)/criteria already applied on the Query tab:
With Data formatted as a structured Table, FILTER formula on the Query tab is:
=LET( Filtered, FILTER(Data, IF(ISBLANK(B4), 1, Data[Application] = B4) * IF(ISBLANK(C4), 1, Data[Type] = C4) * IF(ISBLANK(D4), 1, Data[Series] = D4) * IF(ISBLANK(E4), 1, Data[Size] = E4) * IF(ISBLANK(F4), 1, Data[Geometry] = F4) ), IF(QueryCriterias, Filtered, "Select at least a criterion") )Where QueryCriterias in defined in Name Manager as: =COUNTA(Query!$B$4:$F$4)
- You can use IFS() function to execute filter function based on multiple criteria. Can you please share a sample file so that we can check more. Also can you please show us few scenario that you want.
