Filter in multiple areas

[kalda]

Hello to all,

Im strugling to solve this for a week now. I would like to create filter something like: =FILTER(I4:I97,(J23:J40="PN")+(J42:J59="PN")+(J61:J78="N")+(J80:J97="N"),"") but always with error. This is just an example, I already tryed multiple variants of this, but still with some error in the end. Sofar I figured out, that this function is not supported in Excel like this (criteria in multiple areas), therefore Im asking here for help. Do you know some workaround? Maybe user defined function will help, but I know nothing about VBA.
And sorry for my English, Im not native speaker.

Thank you all.

 

[Joe4]

Welcome to the Board!

I think you need to filter each section separately, but you should be able to combine them all into one list by using the VSTACK function, i.e.

=VSTACK(FILTER(...),FILTER(...),FILTER(...),FILTER(...))

See: VSTACK Function

If that is not what you are looking for, then I probably do not understand your question, in which case it would be helpful to post what your data looks like and expected results.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[kalda]

Thank you for your answer, I already tried this, but if one or more filters comes empty (which is expected), or with error, it will create empty ("") cell, which can then affect the cells under the filter array leading to #SPILL! error (houpfly this is correct translation)

 

[Joe4]

I think you need to show us an example of your data, and exactly what you expected output based on that example needs to look like.

 

[kalda]

So, this is my current sheet:

Plachta nová testovací.xlsx
	I	J	K
2		PO	UT
3		1.	2.
4	Name 1	N	N
5	Name 2	N	N
6	Name 3	N	N
7	Name 4	N	N
8	Name 5	N	N
9	Name 6	N	N
10	Name 7	N	N
11	Name 8	N	N
12	Name 9	N	N
13	Name 10	ŘD	N
14	Name 11	N	N
15
16
17
18
19
20
21
22	OCCUPANCY:	12	12
23	Name 12
24	Name 13
25	Name 14
26	Name 15		REC
27	Name 16		PD
28	Name 17	PN
29	Name 18	PN
30	Name 19
31	Name 20
32	Name 21
33
34
35
36
37
38
39
40
41	OCCUPANCY:
42	Name 22	PN	PN
43	Name 23	HOT	PD
44	Name 24
45	Name 25		PD
46	Name 26
47	Name 27	PD
48	Name 28	PD	PD
49	Name 29
50	Name 30	PD
51	Name 31
52	Name 32
53
54
55
56
57
58
59
60	OCCUPANCY:
61	Name 33	D	D
62	Name 34	D	D
63	Name 35	D	REC
64	Name 36	D	D
65	Name 37	D	D
66	Name 38	D	D
67	Name 39	D	D
68	Name 40	SICK	SICK
69	Name 41	D	D
70	Name 42	D	D
71
72
73
74
75
76
77
78
79	OCCUPANCY:	12	12
80	Name 43
81	Name 44		ŘD
82	Name 45
83	Name 46
84	Name 47
85	Name 48
86	Name 49
87	Name 50
88	Name 51
89	Name 52
90	Name 53
91	Name 54
92	Name 55
93
94
95
96
97
98	OCCUPANCY:	0	0
List1

Cell Formulas
Range	Formula
J2:K2	J2	=IF(WEEKDAY(J3,2)=1,"PO",IF(WEEKDAY(J3,2)=2,"UT",IF(WEEKDAY(J3,2)=3,"ST",IF(WEEKDAY(J3,2)=4,"CT",IF(WEEKDAY(J3,2)=5,"PA",IF(WEEKDAY(J3,2)=6,"SO","NE"))))))
K3	K3	=J3+1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J98:X98	Other Type	Icon set		NO
J80:X97,J23:X40,J42:X59,J61:X78,J4:X21	Cell Value	="SD"	text	NO
J80:X97,J23:X40,J42:X59,J61:X78,J4:X21	Cell Value	="NV"	text	NO
J80:X97,J23:X40,J42:X59,J61:X78,J4:X21	Cell Value	="HOT"	text	NO
J80:X97,J23:X40,J42:X59,J61:X78,J4:X21	Cell Value	="AKCE"	text	NO
J80:X97,J23:X40,J42:X59,J61:X78,J4:X21	Cell Value	="ŘD"	text	NO
J80:X97,J23:X40,J42:X59,J61:X78,J4:X21	Cell Value	="REC"	text	NO
J80:X97,J23:X40,J42:X59,J61:X78,J4:X21	Cell Value	="SICK"	text	NO
J80:X97,J23:X40,J42:X59,J61:X78,J4:X21	Cell Value	="PD"	text	NO
J80:X97,J23:X40,J42:X59,J61:X78,J4:X21	Cell Value	="PN"	text	NO

And this is what I would like to get after the FILTER formula is implemented:

Plachta nová testovací.xlsx
	I	J	K
2		PO	UT
3		1.	2.
4	Name 1	N	N
5	Name 2	N	N
6	Name 3	N	N
7	Name 4	N	N
8	Name 5	N	N
9	Name 6	N	N
10	Name 7	N	N
11	Name 8	N	N
12	Name 9	N	N
13	Name 10	ŘD	N
14	Name 11	N	N
15		Name 17	Name 22
16		Name 18
17		Name 22
18
19
20
21
22	OCCUPANCY:	12	12
23	Name 12
24	Name 13
25	Name 14
26	Name 15		REC
27	Name 16		PD
28	Name 17	PN
29	Name 18	PN
30	Name 19
31	Name 20
32	Name 21
33
34
35
36
37
38
39
40
41	OCCUPANCY:
42	Name 22	PN	PN
43	Name 23	HOT	PD
44	Name 24
45	Name 25		PD
46	Name 26
47	Name 27	PD
48	Name 28	PD	PD
49	Name 29
50	Name 30	PD
51	Name 31
52	Name 32
53
54
55
56
57
58
59
60	OCCUPANCY:
61	Name 33	D	D
62	Name 34	D	D
63	Name 35	D	REC
64	Name 36	D	D
65	Name 37	D	D
66	Name 38	D	D
67	Name 39	D	D
68	Name 40	SICK	SICK
69	Name 41	D	D
70	Name 42	D	D
71		Name 27	Name 16
72		Name 28	Name 23
73		Name 30	Name 25
74			Name 28
75
76
77
78
79	OCCUPANCY:	12	12
80	Name 43
81	Name 44		ŘD
82	Name 45
83	Name 46
84	Name 47
85	Name 48
86	Name 49
87	Name 50
88	Name 51
89	Name 52
90	Name 53
91	Name 54
92	Name 55
93
94
95
96
97
List1

Cell Formulas
Range	Formula
J2:K2	J2	=IF(WEEKDAY(J3,2)=1,"PO",IF(WEEKDAY(J3,2)=2,"UT",IF(WEEKDAY(J3,2)=3,"ST",IF(WEEKDAY(J3,2)=4,"CT",IF(WEEKDAY(J3,2)=5,"PA",IF(WEEKDAY(J3,2)=6,"SO","NE"))))))
K3	K3	=J3+1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J80:X97,J23:X40,J42:X59,J61:X78,J4:X21	Cell Value	="SD"	text	NO
J80:X97,J23:X40,J42:X59,J61:X78,J4:X21	Cell Value	="NV"	text	NO
J80:X97,J23:X40,J42:X59,J61:X78,J4:X21	Cell Value	="HOT"	text	NO
J80:X97,J23:X40,J42:X59,J61:X78,J4:X21	Cell Value	="AKCE"	text	NO
J80:X97,J23:X40,J42:X59,J61:X78,J4:X21	Cell Value	="ŘD"	text	NO
J80:X97,J23:X40,J42:X59,J61:X78,J4:X21	Cell Value	="REC"	text	NO
J80:X97,J23:X40,J42:X59,J61:X78,J4:X21	Cell Value	="SICK"	text	NO
J80:X97,J23:X40,J42:X59,J61:X78,J4:X21	Cell Value	="PD"	text	NO
J80:X97,J23:X40,J42:X59,J61:X78,J4:X21	Cell Value	="PN"	text	NO

This is only 3 columns example.
The FILTER formula should be in cells J15, K15 (and so on) and J71, K71 (and so on).

In cell J15 should be filter setup so it will filter names from column I only if in areas J23:J40 or J42:J59 is "PN" or in areas J61:J78 or J80:J97 is "N".
In cell J71 should be filter setup so it will filter names from column I only if in areas J23:J40 or J42:J59 is "PD" or in areas J61:J78 or J80:J97 is "D".
I would like to have the formula with variable arrays.

 

[Joe4]

OK, looks like we just need to add in an IFERROR part to my formula, in case a particular filter doesn't return anything.
So the formula for J15 would look like this:

=VSTACK(IFERROR(FILTER(I23:I32,J23:J32="PN"),""),IFERROR(FILTER(I42:I52,J42:J52="PN"),""),IFERROR(FILTER(I61:I70,J61:J70="PN"),""),IFERROR(FILTER(I80:I92,J80:J92="PN"),""))

I color-coded each separate filter within the VSTACK function so it can easily be seen. Just repeart this format for your other values.

Here is the result for cell J15:

 

[kalda]

Thank you for your reply, after implementing this, VSTACK generate 2 empty cells (see attached image) which in this case isn´t problem, but definitly will be, when I will use it in other columns. Do you have some idea, how to get rid of them?

 

[Joe4]

Try this variation:

=LET(x,VSTACK(IFERROR(FILTER(I23:I32,J23:J32="PN"),""),IFERROR(FILTER(I42:I52,J42:J52="PN"),""),IFERROR(FILTER(I61:I70,J61:J70="PN"),""),IFERROR(FILTER(I80:I92,J80:J92="PN"),"")),FILTER(x,x<>""))

The original part of the formula is in blue. I added the new stuff in red, putting it in a LET function, and filtering the results to filter out the blank values.

 

[kalda]

Thank you very much. You really hepled me. Solved.

 

[Joe4]

You are welcome.
Glad I was able to help!

That is the first time I ever combined the new LET, VSTACK, and FILTER functions together in one formula, so it was a learning experience for me too!