filter on multiple values in same column

[Danny54]

is it possible to filter a sheet of data where I have multiple groups of selections?

ie.. filter a sheet of data where (column a) is greater than 30 days from today and (column b) doesn't contain the word dog or
if (column b) does contains dog, and (column a) is less than 60 days hid that row.

so, my visible data will show all records that are older than 30 days without the word dog and all rows with or without dog that is greater than 60 days.

I messed with filters and advanced filter and thought i would probably have to use a array of some kind (helper column) then filter on that value

Thanks

 

[Akuini]

Hi, @Danny54
Could you post a data sample including the result as expected?

 

[Danny54]

Hi, @Danny54
Could you post a data sample including the result as expected?

Sorry, should have done that

Date	Type
12/5/2010​	Dog
1/1/2021​	Cat
10/1/2020​	Boy
3/4/2020​	Man
11/23/2020​	Dog

Filter Date greater than 30 days and keep those (is before 12/11/20)

Date	Type
12/5/2010​	Dog
10/1/2020​	Boy
3/4/2020​	Man
11/23/2020​	Dog

Now i need to check to see if I have a Type of "Dog" after 11/11/20 and remove them leaving the desired result

Date	Type
12/5/2010​	Dog
10/1/2020​	Boy
3/4/2020​	Man

Thanks

 

[Akuini]

Ok, try this:
You need a helper column, I'm using col D, you can change that in this part: With Range("D1").Resize(UBound(vb, 1), 1)

Sub a1158167a()
'https://www.mrexcel.com/board/threads/filter-on-multiple-values-in-same-column.1158167/
Dim i As Long, n As Long
Dim va, vb

n = Range("A" & Rows.Count).End(xlUp).Row
va = Range("A1:B" & n)
ReDim vb(1 To UBound(va, 1), 1 To 1)


For i = 2 To UBound(va, 1)
a = va(i, 1)
b = va(i, 2)
    
    If (b <> "Dog" And Date - a > 30) Or Date - a > 60 Then vb(i, 1) = 1

Next
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData

Application.ScreenUpdating = False
    With Range("D1").Resize(UBound(vb, 1), 1)
        .Value = vb
        .AutoFilter Field:=1, Criteria1:=1
        .Offset(1).ClearContents
    End With
Application.ScreenUpdating = True
End Sub

Book1
	A	B	C	D
1	Date	Type
2	December 5, 2010	Dog
4	October 1, 2020	Boy
5	March 4, 2020	Man
Sheet4

so, my visible data will show all records that are older than 30 days without the word dog and all rows with or without dog that is greater than 60 days.

I interpreted the requirements in this part:
If (b <> "Dog" And Date - a > 30) Or Date - a > 60 Then vb(i, 1) = 1

"Date" in the code above means today, and "a" is the date in col A

Is this a correct interpretation?

 

[Danny54]

Sweet,
thanks so much. I was scratching my head for days and the solution seems so simple.

Have a wonderful day

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

 

[Danny54]

quick question?

i get a type mismatch on this line
If (b <> "Dog" And Date - a > 30) Or Date - a > 60 Then vb(i, 1) = 1

do i have to have the value for a in a specific format in order to do the substraction?

Thanks

 

[Akuini]

I forgot to declare variable a & b.
variable a should be date (or variant)

Dim a As Date
Dim b As String

 

[Danny54]

would i have a issue if my column "A" has a format of general showing 12/08/2020.

 

[Akuini]

AFAIK, if you format a cell as general then insert a value that Excel evaluate as date then the format will automatically change to Date. So I don't think it will be an issue.
You can try it yourself and see what happen.