Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

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.

share|improve this answer
    
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 May 17 '11 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 May 17 '11 at 17:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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