How to filter for multiple criteria in Excel

Question

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

User · Answer

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.

User · Answer

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

User · Answer

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    E1   Letters E2    m E3    h E4    j   I put the data starting on row 5    A5   Letters A6    A7          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

[excel] How to filter for multiple criteria in Excel?

Examples related to excel

Examples related to excel-2007