FILTER formula with exclusions

Vic001

New Member
Joined
Feb 23, 2025
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi

I the example below is simiLar to another thread where the formula supplied by Domenic worked perfectly, however I wish to use a formula without an actual exclusions list.

Please kindly see if anyone can help me with the correct syntax for the 2nd formula.

The formula below works 100%, but only if I type the exclusions in L2 and L3

=SORT(UNIQUE(FILTER(B2:B8&" "&C2:C8&" "&D2:D8&" "&E2:E8&" - "&F2:F8,MMULT(IF(ISNUMBER(SEARCH(TRANSPOSE(L2:L3),B2:B8&" "&C2:C8&" "&D2:D8&" "&E2:E8&" - "&F2:F8)),1,0),ROW(L2:L3)^0)=0)))


1740385778991.png



Rather than having an exclusions list I would like to include the exclusions in the formula. I have tried the formula below, but it just gives me an error.

=SORT(UNIQUE(FILTER(B2:B8&" "&C2:C8&" "&D2:D8&" "&E2:E8&" - "&F2:F8,MMULT(IF(ISNUMBER(SEARCH(TRANSPOSE({"* Not In","* Weekend"}),B2:B8&" "&C2:C8&" "&D2:D8&" "&E2:E8&" - "&F2:F8)),1,0),ROW({"* Not In","* Weekend"})^0)=0)))

Many Thanks
Vic
 
Hi, maybe you could try this.

Book1
ABCDEFGHI
1CompanyBranchDepartmentRoleWork StatusDHL Sherburn CFS Van Driver - At Work
2MonLombards TransportGarfordDispatchVan DriverAt WorkEddie Stobart Leeds Transport Van Driver - Holiday
3TueEddie StobartLeedsTransportVan DriverHolidayLombards Transport Garford Dispatch Van Driver - At Work
4WedEddie StobartLeedsTransportVan DriverHoliday
5ThuEddie StobartChicargoTransportVan DriverNot In
6FriDHLSherburnCFSVan DriverAt Work
7SatLombards TransportGarfordDispatchVan DriverWeekend
8SunLombards TransportGarfordDispatchVan DriverWeekend
Sheet1
Cell Formulas
RangeFormula
I1:I3I1=SORT(UNIQUE(FILTER(B2:B8&" "&C2:C8&" "&D2:D8&" "&E2:E8&" - "&F2:F8,NOT(ISNUMBER(MATCH(F2:F8,{"Not In","Weekend"},0))))))
Dynamic array formulas.
 
Upvote 0
Solution

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top