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.

Is there a way to filter columns 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

If I cannot filter by column, is there another way to reach a similar result?

share|improve this question
    
Could you give a short example of your table? I'm not sure what column A - cell data in column B would be if they are text. –  Daniel X Moore May 6 '11 at 23:17
    
Ok, it could look something like this: goo.gl/idyXy /// Column A contains new data, Column B contains data that should be filtered and Column C contains the result. –  Pongy May 7 '11 at 7:09
add comment

1 Answer

up vote 2 down vote accepted

Solution

Place this formula in cell C2:

=ARRAYFORMULA(SORT(UNIQUE(IF(ISNA(MATCH(A2:A,B2:B,0)),A2:A,""))))

What you want is the set difference. This formula will give you all the unique values of column A that do not appear in column B.

Details

MATCH(search_criterion, lookup_array, [match_type]) checks if an element is found in the given array. In this case it checks the element from column A in the entire array of B. If it is not found it returns NA.

IF(ISNA(...), A2:A, "") returns the contents of cell from column A if it was not matched, or an empty string if it was.

SORT(UNIQUE(...)) is optional, but will filter the results in alphabetical order and remove any duplicates which is probably what you want.

Finally ARRAYFORMULA returns the result as an array so that it populates the entire C column.

More information:

share|improve this answer
    
Thank you, that seems to work very well! Much appreciated! –  Pongy May 12 '11 at 14:36
    
Is there a way to also filter strings found in both A and B? Like, I have a domain name in column B (example.com) so all e-mail addresses ending with example.com are excluded from column C. –  Pongy May 13 '11 at 15:43
    
You'll need to have the raw data in a separate sheet or column from the filtered data. Afther that for the filtered dat you can use something like: =FILTER(rawData!A:A, isError(search("example.com", rawData!A:A))) If you need more info open a new question. –  Daniel X Moore May 15 '11 at 7:06
    
Thanks for your advice so far! I created a new question as per your suggestion: goo.gl/H33PW –  Pongy May 16 '11 at 15:43
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.