Advanced Excel Filter

Copper Contributor

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.

NameQuestionAnswer
NakulondaDo you have a valid driver's licenceNo
EsterDo you have a valid driver's licenceYes
MENESIADo you have a valid driver's licenceNo
CiaraDo you have a valid driver's licenceYes
Llewellyn NamuandiDo you have a valid driver's licenceYes
Denzel NelDo you have a valid driver's licenceYes
Nekulilo AlugongoIn what range does your expected monthly salary fall?NAD1000 - NAD4999
MAANO PATEMOSHELA SHEVANYENGAIn what range does your expected monthly salary fall?NAD1000 - NAD4999
Lukas IilongaIn what range does your expected monthly salary fall?NAD1000 - NAD4999
Ndjiisanee NgupandjaraIn what range does your expected monthly salary fall?NAD1000 - NAD4999
Gerson ThomasIn what range does your expected monthly salary fall?NAD1000 - NAD4999
EsterIn what range does your expected monthly salary fall?NAD1000 - NAD4999
MENESIA NEKWAYAIn what range does your expected monthly salary fall?NAD1000 - NAD4999
CiaraIn what range does your expected monthly salary fall?NAD1000 - NAD4999
Maria EgumboIn what range does your expected monthly salary fall?NAD1000 - NAD4999
Shane In what range does your expected monthly salary fall?NAD5000 - NAD9999
Perpetua NakulondaIn what range does your expected monthly salary fall?NAD5000 - NAD9999
HaufikuIn what range does your expected monthly salary fall?NAD5000 - NAD9999
Muzuva MungundaIn what range does your expected monthly salary fall?NAD10000 - NAD14999
DenzyIn what range does your expected monthly salary fall?NAD10000 - NAD14999
NamuandiIn what range does your expected monthly salary fall?NAD15000 - NAD19999
OlgaIn what range does your expected monthly salary fall?NAD30000 - NAD34999

 

 

Thanking you in anticipation for you assistance.

 

Elmo

4 Replies

@Elmo_Erasmus 

=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.

@OliverScheurich 

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.

@Elmo_Erasmus 

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.

@OliverScheurich
Thanks a lot, will check it out.