[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3960
  • Last Modified:

excel filter multiples does not contain

Is there any way in excel to acheive the following, I am trying to filter a huge record set based on "does not contain" type text filters. However I have about 15 does not contain filters, yet when constructing your filter criteria it only seems to give you one "and/or" option. Any workaround?
0
pma111
Asked:
pma111
  • 2
  • 2
  • 2
  • +1
2 Solutions
 
[ fanpages ]IT Services ConsultantCommented:
Hi,

You could use Advanced Criteria Filtering:

"Filter by using advanced criteria"
[ http://office.microsoft.com/en-gb/excel-help/filter-by-using-advanced-criteria-HP005200178.aspx ]

Here you specify the fifteen items in row 2 to row 16 above the data to be filtered.  The first row must contain the same column heading of the data to be filtered.

You would then opt to use the "Advanced Filter" function, set the "Filter the list, in-place" Action, set the "List range" to be the original set of data (including the column heading), & the "Criteria range" to be the first sixteen rows in the same column as the data to be filtered.

BFN,

fp.
0
 
pma111Author Commented:
Does the above not "include" all results based on your range, as opposed to cutting them out the results?
0
 
Rory ArchibaldCommented:
It depends what you enter into the filter rows:

<>*text1*

would filter out items that contain "text1" somewhere in them, for example
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
pma111Author Commented:
could anyone upload an example please as I am struggling with what you are recommending.
0
 
Rory ArchibaldCommented:
If you want to AND multiple conditions (as you do here), you need to repeat the column header, so your criteria range would look like:

Field1          Field1         Field1
<>*test1*   <>*text2*  <>*text3*

Here's a file showing two ways - the one described, and a criteria formula approach.
Advanced-filter-exclusions.xlsx
0
 
Saqib Husain, SyedEngineerCommented:
You can also use one helper column and use a formula like

=or(NOT(ISERR(FIND("abc",A1))),NOT(ISERR(FIND("def",B1))),NOT(ISERR(FIND("ghi",C1))),....)

which covers all conditions and returns a true or false. You can then filter on this column.
0
 
Saqib Husain, SyedEngineerCommented:
Already covered by rorya so ignore mine.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now