Filter on decremental list

[Dirkske72]

I'm trying to build a planning sheet for drivers on different routes based on their availability and knowledge of the routes. Let me try to clarify this : I have 5 drivers, each of them knows 2 different routes, they all have availabilities from monday to friday. I need to plan for a given day for each route a driver according to it's knowledge and availability. Of course each driver can only drive one route. There is also my problem I'm using a Filter function that looks like this =FILTER($B$4:$B$8,($C$4:$C$8=L4)*($F$4:$F$8="x"),FILTER($B$4:$B$8,($D$4:$D$8=L4)*($F$4:$F$8="x"),"OPEN")), problem is that I searches everytime the whole list of drivers, where it shopuld be able to search only drivers that haven't been selected yet. I haven't found a solution for that last bit... Any help on this ?

Driver planning.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2			Prefered			Available						Planning
3		Driver	Route 1	Route 2		Mon	Tue	Wed	Thu	Fri		Route	Mon
4		D1	R1	R3		x	x	x	x	x		R1	D1
5		D2	R2	R4								R2	D3
6		D3	R4	R2		x	x	x	x	x		R3	D4
7		D4	R3	R5		x	x	x	x	x		R4	D3
8		D5	R5	R1		x	x	x	x	x		R5	D5
9
Sheet1

Cell Formulas
Range	Formula
M4:M8	M4	=FILTER($B$4:$B$8,($C$4:$C$8=L4)*($F$4:$F$8="x"),FILTER($B$4:$B$8,($D$4:$D$8=L4)*($F$4:$F$8="x"),"OPEN"))

 

[Peter_SSs]

problem is that I searches everytime the whole list of drivers, where it shopuld be able to search only drivers that haven't been selected yet. I haven't found a solution for that last bit... Any help on this ?

Welcome to the MrExcel board!
Does this cover that part of it?

24 10 15.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M
1
2		Prefered		Available		Planning
3	Driver	Route 1	Route 2		Mon	Tue	Wed	Thu	Fri		Route	Mon
4	D1	R1	R3		x	x	x	x	x		R1	D1
5	D2	R2	R4								R2	D3
6	D3	R4	R2		x	x	x	x	x		R3	D4
7	D4	R3	R5		x	x	x	x	x		R4	OPEN
8	D5	R5	R1		x	x	x	x	x		R5	D5
Drivers

Cell Formulas
Range	Formula
M4:M8	M4	=FILTER(B$4:B$8,(C$4:C$8=L4)*(F$4:F$8="x")*ISNA(MATCH(B$4:B$8,M$3:M3,0)),FILTER(B$4:B$8,(D$4:D$8=L4)*(F$4:F$8="x")*ISNA(MATCH(B$4:B$8,M$3:M3,0)),"OPEN"))

 

[Dirkske72]

Thanks this does indeed solve it. Could you explain a bit your solution (for my understanding) please ?

 

[Peter_SSs]

Your filters filter the drivers based on their knowledge of the route and their availability. Mine add one more condition and that is that their name does not already exist in column M above the cell that the particular formula is in
ISNA(MATCH(B$4:B$8,M$3:M3,0))
If a driver name is not in the column above the formula then trying to MATCH it will produce an #N/A error so ISNA will be true so that driver is still in the mix (provided the other conditions are also true for them)

Example. In cell M7, drivers that know the route are D2 and D3. However, D2 is not available so that leaves D3. But D3 already exists above cell M7 (in M5) so D3 is also ruled out, leaving nobody. Hence OPEN

 

[Dirkske72]

Thanks for the clarification, that is exactly what I was looking for, but I could find a way to implement it. Now I also understand how you solved it

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.