FILTER formula with exclusions

[Vic001]

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)))

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

 

[FormR]

Hi, maybe you could try this.

Book1
	A	B	C	D	E	F	G	H	I
1		Company	Branch	Department	Role	Work Status			DHL Sherburn CFS Van Driver - At Work
2	Mon	Lombards Transport	Garford	Dispatch	Van Driver	At Work			Eddie Stobart Leeds Transport Van Driver - Holiday
3	Tue	Eddie Stobart	Leeds	Transport	Van Driver	Holiday			Lombards Transport Garford Dispatch Van Driver - At Work
4	Wed	Eddie Stobart	Leeds	Transport	Van Driver	Holiday
5	Thu	Eddie Stobart	Chicargo	Transport	Van Driver	Not In
6	Fri	DHL	Sherburn	CFS	Van Driver	At Work
7	Sat	Lombards Transport	Garford	Dispatch	Van Driver	Weekend
8	Sun	Lombards Transport	Garford	Dispatch	Van Driver	Weekend
Sheet1

Cell Formulas
Range	Formula
I1:I3	I1	=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.

 

[Vic001]

=SORT(UNIQUE(FILTER(B2:B8&" "&C2:C8&" "&D2:D8&" "&E2:E8&" - "&F2:F8,NOT(ISNUMBER(MATCH(F2:F8,{"Not In","Weekend"},0))))))

Hi
Thanks very much, that worked 100%