Wednesday, May 18, 2011

yogi_Apply Two Filter Functions In Spreadsheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
Pettere said:
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.

Update by Petteri:

In column A I have new e-mail addresses.
In column B I have a filter list consisting of e-mail addresses and domains.
Column C should be the result of all the e-mail addresses in column A minus the e-mail addresses in column B, however the e-mail addresses from column A which have a corresponding domain name in column B should also be excluded.

Example:

A: jimmy@itcompany.com, arne@itcompany.com, fred@webcompany.com
B: jimmy@itcompany.com, webcompany.com
C: arne@itcompany.com

Column A is the new list.
Column B is the filter list (e-mail addresses and the occasional domain)
Column C is the result, A minus B.

--------------------------------------------------------------------------------------------