filter on multiple values in same column

Danny54

Board Regular
Joined
Jul 3, 2019
Messages
150
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,948
Office Version
  1. 365
Platform
  1. Windows
Hi, @Danny54
Could you post a data sample including the result as expected?
 

Danny54

Board Regular
Joined
Jul 3, 2019
Messages
150
Office Version
  1. 365
Platform
  1. Windows
Hi, @Danny54
Could you post a data sample including the result as expected?


Sorry, should have done that

DateType
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)

DateType
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

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


Thanks
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,948
Office Version
  1. 365
Platform
  1. Windows
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)

VBA Code:
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
ABCD
1DateType
2December 5, 2010Dog
4October 1, 2020Boy
5March 4, 2020Man
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?
 
Solution

Danny54

Board Regular
Joined
Jul 3, 2019
Messages
150
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Have a wonderful day
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,948
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help & thanks for the feedback. :)
 

Danny54

Board Regular
Joined
Jul 3, 2019
Messages
150
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Feb 1, 2016
Messages
2,948
Office Version
  1. 365
Platform
  1. Windows
I forgot to declare variable a & b.
variable a should be date (or variant)
VBA Code:
Dim a As Date
Dim b As String
 

Danny54

Board Regular
Joined
Jul 3, 2019
Messages
150
Office Version
  1. 365
Platform
  1. Windows
would i have a issue if my column "A" has a format of general showing 12/08/2020.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,948
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,913
Messages
5,621,599
Members
415,847
Latest member
AlpinoHirsch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top