1

I am trying to apply two filter functions in Google Spreadsheets:

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

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.

See example file I created: applying two filters

Anyone knows how to do this?

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
2
  • You mean like this (www.goo.gl/idyXy)? It's a bit tedious to replace the domain string in the formula on each and every row. Sure there's no easier way of doing this? I had a guy writing a script for me in Excel that I ran every time I needed filtering so I reckon perhaps there's a way of doing something similar in Google Docs.
    – Pongy
    Commented May 17, 2011 at 17:06
  • Not saying that you should write a script of course, just that there may be a workaround allowing me or whoever reads this to just add data that should be filtered in a column rather than copying/pasting the whole formula into every row. Your help is of course much appreciated, let me know if I can reciprocate somehow.
    – Pongy
    Commented May 17, 2011 at 17:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.