Applying two filter functions in Google Docs Spreadsheets

Question

I am trying to apply two filter functions in Google Docs.

Column (A) contains new data Column (B) contains filter data, i.e. cells with text that should not be included in column C Column (C) is the outcome, i.e. column A minus all the cell data in column B

Example: www.goo.gl/idyXy

I use this formula in column C: =ARRAYFORMULA(SORT(UNIQUE(IF(ISNA(MATCH(A2:A,B2:B,0)),A2:A,""))))

It works well in the sense that it filters data (in this case e-mail addresses) that should not be included in column C. However, I would also like to be able to filter whole domains, i.e. if the domain example.com is listed in column B then all addresses from column A ending with example.com should be excluded from column C.

Anyone knows how to do this?

Kind regards

Rio

Answer 1

You'll need to have the raw data in a separate sheet or column from the filtered data. Here I'm assuming the raw data is in a sheet named rawData column A.

For the filtered dat you can use:

=FILTER(rawData!A:A, isError(search("example.com", rawData!A:A)))

Put this formula in cell A1 of a new sheet where you want the filtered data.