Forum Discussion
Dynamic Filter Multiple Columns
I'm trying to create a dynamic filter based on two columns.
It should list the available values on both columns and upon choosing one of the values from the dropdown, it should filter the table to the results that are present on either of the columns.
Like a slicer from a pivot table that is based on two columns instead of 1.
At the moment I'm going around it by creating a data validation cell listing the available options, adding a dynamic filter to that column and entering a formula that checks if any of the columns matches the dropdown.
Probably the images explain better and also what I'm trying to do:
Columns B and C have the criteria I want to filter by.
D1 has a data validation list of the values available between Color1 and Color2 (Black, White). At the same time the cell is a header for the table and a filter.
D2 then has =IF(OR(B2=D1,C2=D1),D1,""), so when I select something from the dropdown the column would return a value, that I can filter by with cell D1.
Any other way around this?
I'm open to pivots or anything else as long as I can filter and sort the rest of the data.
Thanks!
If you organize the data structure as per what is suggested by Detlef_Lewin, it would be easy for you to generate reports using the Pivot Table and filter it as per your requirement.
Another way is, you can use Power Query to filter the data in the existing format and get the filtered table back on to the sheet.
In the attached, change the color criteria in cell E2 and the table on the right would be updated automatically. This functionality utilizes Change Event code which is placed on Sheet1 Module and it is there in order to avoid manually refreshing the table returned by the Power Query. So once you change the criteria in E2, this code is executed automatically and refresh the Query to return the updated table on the sheet.
See if this is something you can work with.
5 Replies
If you organize the data structure as per what is suggested by Detlef_Lewin, it would be easy for you to generate reports using the Pivot Table and filter it as per your requirement.
Another way is, you can use Power Query to filter the data in the existing format and get the filtered table back on to the sheet.
In the attached, change the color criteria in cell E2 and the table on the right would be updated automatically. This functionality utilizes Change Event code which is placed on Sheet1 Module and it is there in order to avoid manually refreshing the table returned by the Power Query. So once you change the criteria in E2, this code is executed automatically and refresh the Query to return the updated table on the sheet.
See if this is something you can work with.
Very helpful, exactly what I had in mind.
Glad you found it helpful.
Your process is perfectly okay - for your data structure.
You could change your data structure into this:
Clothing Piece Color Shirt White Shirt Black Pants Black Jacket White It's called unpivoting.
Wow... How did I miss that, thanks!
