Advanced Filtering Not working as I would expect

[sheepdemon]

I have a list of entries in a table, and I'm trying to use Advanced Filtering to filter this list in place. The advanced Filtering criteria are in a second sheet and I have them setup as follows :

<>Get*
<>List*

As I understand it this should filter out any entry beginning with "List..." and "Get...". However when I apply this filter to the column I want to apply the filtering on every entry is removed, even entries which do not start with "Get" or "List". I'm not sure if I need to do something specific because of these being 2 seperate criteria, or something.

Any hints, more info can be supplied if required.

 

[awoohaw]

You need to structure your query differently:

Book2
	A	B	C	D	E
1
2
3	Group1	Group2
4	ListIJN	GetPSA
5	GetXRM	GetLNI
6	UWCVE	YLJAX
7	OQTMV	ZVCFN
8	XMWPW	KDLNH
9	COXIN	ZLILL
10	RVJGX	EWBVW
11	GetADB	GetVCK
12	GetTOA	ListNZH
13	ListRFI	GetPEU
14	GetRBB	GetCOQ
15	ListINI	ListYDW
16	GetSJF	ListEAI
17	GetDBO	GetKYH
18	GetIIV	ListOID
19
20
21	Group1			Group1	Group1
22	<>Get*			<>Get*	<>List*
23	<>List*
24
25	Group1	Group2		Group1	Group2
26	ListIJN	GetPSA		MHBHW	QVMLJ
27	GetXRM	GetLNI		PQWNZ	MINJA
28	HLPHF	RFKAJ		DJYVY	JXXTW
29	TYMBY	BLICJ		XIMWT	FKGCM
30	ASIIR	VSZCD		IIDEG	FIQDU
31	UVWBE	BJPQF
32	WLUQK	JJOJM
33	GetADB	GetVCK
34	GetTOA	ListNZH
35	ListRFI	GetPEU
36	GetRBB	GetCOQ
37	ListINI	ListYDW
38	GetSJF	ListEAI
39	GetDBO	GetKYH
40	GetIIV	ListOID
Sheet1

Cell Formulas
Range	Formula
D22,A22	D22	="<>"& "Get*"
E22,A23	E22	="<>"& "List*"

 

[sheepdemon]

The list I am trying to filter :

As you can see there are entries over the "List" / "Get" on here. The Filtering Criteria I have on another sheet :

And the Advanced Filter I'm setting up :

This is resulting in everything on the list being filtered, and I don't understand why.

 

[sheepdemon]

You need to structure your query differently:

Book2
	A	B	C	D	E
1
2
3	Group1	Group2
4	ListIJN	GetPSA
5	GetXRM	GetLNI
6	UWCVE	YLJAX
7	OQTMV	ZVCFN
8	XMWPW	KDLNH
9	COXIN	ZLILL
10	RVJGX	EWBVW
11	GetADB	GetVCK
12	GetTOA	ListNZH
13	ListRFI	GetPEU
14	GetRBB	GetCOQ
15	ListINI	ListYDW
16	GetSJF	ListEAI
17	GetDBO	GetKYH
18	GetIIV	ListOID
19
20
21	Group1			Group1	Group1
22	<>Get*			<>Get*	<>List*
23	<>List*
24
25	Group1	Group2		Group1	Group2
26	ListIJN	GetPSA		MHBHW	QVMLJ
27	GetXRM	GetLNI		PQWNZ	MINJA
28	HLPHF	RFKAJ		DJYVY	JXXTW
29	TYMBY	BLICJ		XIMWT	FKGCM
30	ASIIR	VSZCD		IIDEG	FIQDU
31	UVWBE	BJPQF
32	WLUQK	JJOJM
33	GetADB	GetVCK
34	GetTOA	ListNZH
35	ListRFI	GetPEU
36	GetRBB	GetCOQ
37	ListINI	ListYDW
38	GetSJF	ListEAI
39	GetDBO	GetKYH
40	GetIIV	ListOID
Sheet1

Cell Formulas
Range	Formula
D22,A22	D22	="<>"& "Get*"
E22,A23	E22	="<>"& "List*"

I only have one list, and i was hoping to apply both criteria to that list. Is that even possible? Ideally multiple criteria but I was starting with 2 to ensure it worked, which clearly it did not.

 

[awoohaw]

I cannot filter when the criteria range is on a different workbook.
But you cannot filter two "not equals to" mutually exclusivve items. You'll get everything.
You have to have them on the same line.

 

[awoohaw]

I only have one list, and i was hoping to apply both criteria to that list. Is that even possible? Ideally multiple criteria but I was starting with 2 to ensure it worked, which clearly it did not.

the second example in my first post does that.
And you have not acknowleged if the formula structure I suggested worked or did not work.

 

[awoohaw]

Okay, I got it to work with criteria on a different sheet. I used the structure I told you above in POST2.
Before filter:
(note, I errored while copying the xl2bb data range, this starts at cell A2, be sure you paste appropriately).

Book2
	A	B	C	D	E	F	G	H
2
3	Correlaton ID	Operation Name	Status	Event Cat	Level	Time	Subscription
4	ListIJN	GetPSA	Succeeded	Admin	4	13:00:00	RSDRP
5	GetXRM	GetLNI	Started	Admin	4	11:00:00	CYLZC
6	UWCVE	YLJAX	Started	Admin	4	10:00:00	RZTKI
7	OQTMV	ZVCFN	Succeeded	Admin	4	12:00:00	JINGR
8	XMWPW	KDLNH	Succeeded	Admin	4	16:00:00	PMDXV
9	COXIN	ZLILL	Started	Admin	4	15:00:00	KIXQE
10	RVJGX	EWBVW	Started	Admin	4	10:00:00	OKOCD
11	GetADB	GetVCK	Started	Admin	4	10:00:00	UFESX
12	GetTOA	ListNZH	Succeeded	Admin	4	09:00:00	LRIRG
13	ListRFI	GetPEU	Started	Admin	4	10:00:00	ZBHVK
14	GetRBB	GetCOQ	Succeeded	Admin	4	13:00:00	IYNPQ
15	ListINI	ListYDW	Succeeded	Admin	4	10:00:00	PSBOY
16	GetSJF	ListEAI	Succeeded	Admin	4	15:00:00	CGMDF
17	GetDBO	GetKYH	Succeeded	Admin	4	10:00:00	SKVRR
18	GetIIV	ListOID	Succeeded	Admin	4	11:00:00	QPHJX
Sheet1

Filter Criteria:

Result:

Book2
	A	B	C	D	E	F	G
1
2
3	Correlaton ID	Operation Name	Status	Event Cat	Level	Time	Subscription
6	UWCVE	YLJAX	Started	Admin	4	10:00:00	RZTKI
7	OQTMV	ZVCFN	Succeeded	Admin	4	12:00:00	JINGR
8	XMWPW	KDLNH	Succeeded	Admin	4	16:00:00	PMDXV
9	COXIN	ZLILL	Started	Admin	4	15:00:00	KIXQE
10	RVJGX	EWBVW	Started	Admin	4	10:00:00	OKOCD
19
Sheet1

I also got it to work with copy to a new location:

Book2
	A	B	C	D	E	F	G
1
2
3	Correlaton ID	Operation Name	Status	Event Cat	Level	Time	Subscription
4	ListIJN	GetPSA	Succeeded	Admin	4	13:00:00	RSDRP
5	GetXRM	GetLNI	Started	Admin	4	11:00:00	CYLZC
6	UWCVE	YLJAX	Started	Admin	4	10:00:00	RZTKI
7	OQTMV	ZVCFN	Succeeded	Admin	4	12:00:00	JINGR
8	XMWPW	KDLNH	Succeeded	Admin	4	16:00:00	PMDXV
9	COXIN	ZLILL	Started	Admin	4	15:00:00	KIXQE
10	RVJGX	EWBVW	Started	Admin	4	10:00:00	OKOCD
11	GetADB	GetVCK	Started	Admin	4	10:00:00	UFESX
12	GetTOA	ListNZH	Succeeded	Admin	4	09:00:00	LRIRG
13	ListRFI	GetPEU	Started	Admin	4	10:00:00	ZBHVK
14	GetRBB	GetCOQ	Succeeded	Admin	4	13:00:00	IYNPQ
15	ListINI	ListYDW	Succeeded	Admin	4	10:00:00	PSBOY
16	GetSJF	ListEAI	Succeeded	Admin	4	15:00:00	CGMDF
17	GetDBO	GetKYH	Succeeded	Admin	4	10:00:00	SKVRR
18	GetIIV	ListOID	Succeeded	Admin	4	11:00:00	QPHJX
19
20
21
22	Correlaton ID	Operation Name	Status	Event Cat	Level	Time	Subscription
23	UWCVE	YLJAX	Started	Admin	4	10:00:00	RZTKI
24	OQTMV	ZVCFN	Succeeded	Admin	4	12:00:00	JINGR
25	XMWPW	KDLNH	Succeeded	Admin	4	16:00:00	PMDXV
26	COXIN	ZLILL	Started	Admin	4	15:00:00	KIXQE
27	RVJGX	EWBVW	Started	Admin	4	10:00:00	OKOCD
Sheet1

 

[sheepdemon]

Hi,

Apologies for delay in updating, Friday was a nightmare and then the weekend happened.

Your suggestion worked perfectly.

Thanks,

 

[awoohaw]

I'm happy it worked for you.

One thing to reduce keystrokes is you could write your criteria formulas a little differently
="<>"& "Get*" could be: ="<>Get*"
="<>"& "List*" could be: ="<>List*"

I have not tried them, but you're welcome to.

Regardless, Best Wishes!