Mar 19 2022 06:33 AM - edited Mar 19 2022 06:34 AM
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 applied, only Ester and Ciara records should remain.
Name | Question | Answer |
Nakulonda | Do you have a valid driver's licence | No |
Ester | Do you have a valid driver's licence | Yes |
MENESIA | Do you have a valid driver's licence | No |
Ciara | Do you have a valid driver's licence | Yes |
Llewellyn Namuandi | Do you have a valid driver's licence | Yes |
Denzel Nel | Do you have a valid driver's licence | Yes |
Nekulilo Alugongo | In what range does your expected monthly salary fall? | NAD1000 - NAD4999 |
MAANO PATEMOSHELA SHEVANYENGA | In what range does your expected monthly salary fall? | NAD1000 - NAD4999 |
Lukas Iilonga | In what range does your expected monthly salary fall? | NAD1000 - NAD4999 |
Ndjiisanee Ngupandjara | In what range does your expected monthly salary fall? | NAD1000 - NAD4999 |
Gerson Thomas | In what range does your expected monthly salary fall? | NAD1000 - NAD4999 |
Ester | In what range does your expected monthly salary fall? | NAD1000 - NAD4999 |
MENESIA NEKWAYA | In what range does your expected monthly salary fall? | NAD1000 - NAD4999 |
Ciara | In what range does your expected monthly salary fall? | NAD1000 - NAD4999 |
Maria Egumbo | In what range does your expected monthly salary fall? | NAD1000 - NAD4999 |
Shane | In what range does your expected monthly salary fall? | NAD5000 - NAD9999 |
Perpetua Nakulonda | In what range does your expected monthly salary fall? | NAD5000 - NAD9999 |
Haufiku | In what range does your expected monthly salary fall? | NAD5000 - NAD9999 |
Muzuva Mungunda | In what range does your expected monthly salary fall? | NAD10000 - NAD14999 |
Denzy | In what range does your expected monthly salary fall? | NAD10000 - NAD14999 |
Namuandi | In what range does your expected monthly salary fall? | NAD15000 - NAD19999 |
Olga | In what range does your expected monthly salary fall? | NAD30000 - NAD34999 |
Thanking you in anticipation for you assistance.
Elmo
Mar 19 2022 07:35 AM
=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.
Mar 22 2022 01:38 AM
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.
Mar 22 2022 09:22 AM
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.