Filter table of entries and exclude any that are found in another table in Excel

Automation Monkey 0
Viewed 35

I have a table of staff data which I am filtering based on their employment status not being terminated and if they're a development role. To do this I have the following function :

=FILTER(FILTER(StaffDetailsTbl,(StaffDetailsTbl[Employment Status]<>"Employment Terminated")*(StaffDetailsTbl[Dev Role]="Yes"))),{1,1,1,0,0,0,1,0,0})

I have another table called AllStaffProjectAllocationTbl which has a matching Employee column and I'd like to check if the employee in the StaffDetailsTbl exists in AllStaffProjectAllocationTbl and if they do exclude them. I have first started to try and find if there is a match and include them only, with the view I can then flip this logic somehow to exclude the matches

=FILTER(FILTER(StaffDetailsTbl,(StaffDetailsTbl[Employment Status]<>"Employment Terminated")*(StaffDetailsTbl[Dev Role]="Yes")*(MATCH(StaffDetailsTbl[Employee],AllStaffProjectAllocationTbl,AllStaffProjectAllocationTbl[Employee]))),{1,1,1,0,0,0,1,0,0})

Unfortunately this is giving me an error with the match. Does anyone have any advice on how I can approach this please?

excel-formula vba excel
1 Answers

The following solved it:

=FILTER(FILTER(StaffDetailsTbl,(StaffDetailsTbl[Employment Status]<>"Employment Terminated")*(StaffDetailsTbl[Dev Role]="Yes")*(IF(ISERROR(VLOOKUP(StaffDetailsTbl[Employee], AllStaffProjectAllocationTbl, 1, FALSE)),1,0 ))),{1,1,1,0,0,0,1,0,0})

By multiplying the filter by IF(ISERROR(VLOOKUP(StaffDetailsTbl[Employee], AllStaffProjectAllocationTbl, 1, FALSE)),1,0 ) it filters only those entries from the StaffDetailsTbl Table that do not exist in the AllStaffProjectAllocationTbl Table.

An alternative solution from Jos Woolley using NOT(ISNUMBER(MATCH(StaffDetailsTbl[Employee],AllStaffProjectAllocationTbl[Employee],0))) also works, giving a final solution of:

=FILTER(FILTER(StaffDetailsTbl,(StaffDetailsTbl[Employment Status]<>"Employment Terminated")*(StaffDetailsTbl[Dev Role]="Yes")*( NOT(ISNUMBER(MATCH(StaffDetailsTbl[Employee],AllStaffProjectAllocationTbl[Employee],0))))),{1,1,1,0,0,0,1,0,0})
Automation Monkey0
Related