Forum Discussion
Elmo_Erasmus
Mar 19, 2022Copper Contributor
Advanced Excel Filter
In the table below, I want to filter rows where the Job Applicant (Name column), has a driver’s license and the expected salary range is NAD1000 – NAD4999 in the Answer Column. Once the filter is ap...
OliverScheurich
Mar 19, 2022Gold Contributor
=LET(name,A2:A23,
licence,COUNTIFS($A$2:$A$23,name,$B$2:$B$23,"Do you have a valid driver's licence",$C$2:$C$23,"Yes")=1,
salary,COUNTIFS($A$2:$A$23,name,$B$2:$B$23,"In what range does your expected monthly salary fall?",$C$2:$C$23,"NAD1000 - NAD4999")=1,
result,UNIQUE(FILTER(name,licence+salary=2)),
result)If you work with Office365 or 2021 or Excel online you can try above formula which seems to work in my sheet.
- Elmo_ErasmusMar 22, 2022Copper Contributor
Thanks for your suggestion. However I was hoping for a solution that can filter the records in the Excel table, making use of criteria from cell values so that the user don't have to change formulas, but simply change the criteria in the cells to apply different filters. Almost like using the Advanced Filter option in Excel.
- OliverScheurichMar 22, 2022Gold Contributor
In the attached file you can enter the filter criteria in cells G1:J1 and then click the button in cell F3 to execute the macro for the advanced filter.
- Elmo_ErasmusMar 22, 2022Copper ContributorOliverScheurich
Thanks a lot, will check it out.