CODEWITHSUNDEEP
excelexcel-2007
TruMan1
TruMan1

Reputation: 36078

How to filter for multiple criteria in Excel?

I am trying to filter for multiple criteria, but I see that the "Filter" option only has 2 fields for "AND/OR" options. I have a column full of links. I want to extract all rows that contain these in it:

.pdf .doc .docx .xls .xlsx .rtf .txt .csv .pps

Is there a good way to do this?

Upvotes: 6

Views: 127001

Answers (3)

Joshua
Joshua

Reputation: 367

You can pass an array as the first AutoFilter argument and use the xlFilterValues operator.

This will display PDF, DOC and DOCX filetypes.

Criteria1:=Array(".pdf", ".doc", ".docx"), Operator:=xlFilterValues

Upvotes: 3

Justin Ohms
Justin Ohms

Reputation: 3523

Maybe not as elegant but another possibility would be to write a formula to do the check and fill it in an adjacent column. You could then filter on that column.

The following looks in cell b14 and would return true for all the file types you mention. This assumes that the file extension is by itself in the column. If it's not it would be a little more complicated but you could still do it this way.

=OR(B14=".pdf",B14=".doc",B14=".docx",B14=".xls",B14=".xlsx",B14=".rtf",B14=".txt",B14=".csv",B14=".pps")

Like I said, not as elegant as the advanced filters but options are always good.

Upvotes: 4

Edward Leno
Edward Leno

Reputation: 6327

The regular filter options in Excel don't allow for more than 2 criteria settings. To do 2+ criteria settings, you need to use the Advanced Filter option. Below are the steps I did to try this out.

http://www.bettersolutions.com/excel/EDZ483/QT419412321.htm

Set up the criteria. I put this above the values I want to filter. You could do that or put on a different worksheet. Note that putting the criteria in rows will make it an 'OR' filter and putting them in columns will make it an 'AND' filter.

  1. E1 : Letters
  2. E2 : =m
  3. E3 : =h
  4. E4 : =j

I put the data starting on row 5:

  1. A5 : Letters
  2. A6 :
  3. A7 :
  4. ...

Select the first data row (A6) and click the Advanced Filter option. The List Range should be pre-populated. Select the Criteria range as E1:E4 and click OK.

That should be it. Note that I use the '=' operator. You will want to use something a bit different to test for file extensions.

Upvotes: 5

Related Questions