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 · Accepted 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

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

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

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

Examples related to excel

Examples related to excel-2007