Advanced Filtering

[ItalianPlatinum]

Hello - Having a roadblock on filtering using VBA. I have a simple dataset where I need two columns criteria to be met. The issue i am having is. if level date is 2 but the level date is NOT 8/24 i want it to show but my filter is filtering it out. How can I get the filtering to be smart enough to say if both are met to filter it out, but only if both are met. My table below starts on row 5.

			20200824		8/24/2020
			24-Aug-20​
Desc 1	Desc 2	Date 1	Date 2	Level	Level Date	indicator
1	A	20200823​	20200823​	0	0​	AA
2	B	20200823​	0​	0	0​	BB
3	C	20200823​	0​	0	0​	BB
4	D	20200823​	20200823​	1	23-Aug-20​	CC
5	E	20200823​	20200823​	1	24-Aug-20​	CC
6	F	20200823​	20200823​	2	23-Aug-20​	DD
7	G	20200823​	20200823​	1	24-Aug-20​	DD

Sub TEST2()
If Not ActiveSheet.AutoFilterMode Then ActiveSheet.Range("10:10").AutoFilter

Range("7:7").AutoFilter Field:=7, Criteria1:="AA", Operator:=xlOr, Criteria2:="DD"
Range("7:7").AutoFilter Field:=5, Criteria1:="<>2", Operator:=xlOr, Criteria2:="*<>2*"
Range("7:7").AutoFilter Field:=3, Criteria1:="<>" & Range("D6")
Range("7:7").AutoFilter Field:=3, Criteria1:="<>" & Range("D5")

End Sub

 

[jasonb75]

Could you be a bit clearer on exactly what should be filtered please?

You say that you're trying to filter based on level (field 5) and level date (field 6) but your code is filtering the dates in field 3. Does this mean that the date filter is to be applied on both date columns?

Also, what exactly are you trying to do with the field 5 criteria? "*<>2*" do you actually have <>2 in some cells, or should it be "<>*2*"?

 

[Peter_SSs]

This is extremely confusing!!

My table below starts on row 5.

But the table shows five rows above with the headings on row 6
.. the code appears to be applying Autofilter from row 10 ( ActiveSheet.Range("10:10").AutoFilter )
.. but then from row 7 ( Range("7:7").AutoFilter ... )

Where does the AutoFilter start (row 5, 6, 7, 10 or somewhere else)?

 

[ItalianPlatinum]

Alright well for starters sorry for apparently confusing everyone. I inadvertently pasted the wrong code it should have been this:

If Not ActiveSheet.AutoFilterMode Then ActiveSheet.Range("10:10").AutoFilter

Range("10:10").AutoFilter Field:=7, Criteria1:="AA", Operator:=xlOr, Criteria2:="DD"
Range("10:10").AutoFilter Field:=5, Criteria1:="<>2", Operator:=xlOr, Criteria2:="*<>2*"
Range("10:10").AutoFilter Field:=6, Criteria1:="<>" & Range("D6")
Range("10:10").AutoFilter Field:=3, Criteria1:="<>" & Range("D5")

The reason for this Range("10:10").AutoFilter Field:=5, Criteria1:="<>2", Operator:=xlOr, Criteria2:="*<>2*" is the data set could have the #2 or test value of 2 so was trying to solution both but that part has always failed so I sort have been living with it not yielding the correct filter

Data should start at row 10 and filter on row 10. ultimately looking for:

Field 3: if doesn't = today's date to show (format 20200824)
Field 6: if doesn't = today's date to show (format 24-Aug-20)
Field 5: if doesn't = value 2 (number or text)
Field 7: If = AA or DD only.

When all said and done. i should have two records AA and the first DD listed above.

 

[jasonb75]

The only way that I know of to do this is with a logical test in an additional column.

I've tried a few things but not getting the expected output, I can see what it should be but can't get it right for some reason. Going to put it down for a while and have another look later. Hopefully @Peter_SSs will return with some fresh ideas before I lose what little sanity I have left ?

 

[jasonb75]

This is the part where I'm getting the logic wrong, looking at the last 4 rows in this edited example, which ones should be shown after the filter is applied?

Bear in mind that we're assuming today is the 24th in order to keep the test consistent.

25.08.20.xlsm
	E	F	G
10	Level	Level Date	indicator
11	0	0	AA
12	0	0	BB
13	1	0	BB
14	1	23/08/2020	DD
15	1	24/08/2020	DD
16	2	23/08/2020	DD
17	2	24/08/2020	DD
Sheet24

 

[ItalianPlatinum]

This is the part where I'm getting the logic wrong, looking at the last 4 rows in this edited example, which ones should be shown after the filter is applied?

Bear in mind that we're assuming today is the 24th in order to keep the test consistent.

25.08.20.xlsm
	E	F	G
10	Level	Level Date	indicator
11	0	0	AA
12	0	0	BB
13	1	0	BB
14	1	23/08/2020	DD
15	1	24/08/2020	DD
16	2	23/08/2020	DD
17	2	24/08/2020	DD
Sheet24

Row 11 and Row 16 looking to show. basically looking for the level to be 2 AND level date to be today. so the and is driving me nuts. and also getting it so the 2 can be either value or text.

 

[ItalianPlatinum]

Row 11 and Row 16 looking to show. basically looking for the level to be 2 AND level date to be today. so the and is driving me nuts. and also getting it so the 2 can be either value or text.

wait your rows 14 and 15 should be CC

 

[jasonb75]

wait your rows 14 and 15 should be CC

No, I deliberately changed DD to DD and changed E17 from 1 to 2 in order to give 4 different combinations of Level and Level date whilst using DD as a constant control value, and asked

looking at the last 4 rows in this edited example, which ones should be shown after the filter is applied?

 

[ItalianPlatinum]

No, I deliberately changed DD to DD and changed E17 from 1 to 2 in order to give 4 different combinations of Level and Level date whilst using DD as a constant control value, and asked

Ahhh you did say that yes so with DD then it should be rows 10, 14, 16