I am trying to automate some filtering in hopes of eventually including the filters in a Macro down the line. I am trying to filter by multiple criteria, as below:
Column 1: does not contain Plant A, Plant B, Plant C, or Plant D; include all other values
Column 2: contains either "x" or "y" somewhere in the field
I am unable to find a good example of Advanced Filters that combines "contains" with "does not contain."
Help!!
Excel will only allow two values for its built-in filters. But, you could use a column of formulas for your specific filtering needs - say that your columns are A and B, with data starting in row 2:
=AND(ISERROR(FIND("Plant A",A2)),ISERROR(FIND("Plant B",A2)),ISERROR(FIND("Plant C",A2)),ISERROR(FIND("Plant D",A2)),OR(NOT(ISERROR(FIND("x",B2))),NOT(ISERROR(FIND("y",B2)))))
and copy down, then filter that column for TRUE.
Bernie Deitrick
Excel MVP 2000-2010
There are only two criteria available for "does not contain." Think you're going to need to put a formula in a column and filter on that.
=OR(ISNUMBER(SEARCH({"Plant A","Plant B","Plant C"}, A2)))
Last edited by shg; 11-05-2018 at 01:56 PM.
Entia non sunt multiplicanda sine necessitate
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks