Google Sheets – Remove Values in a Range if They Appear in Another Range

google sheetsgoogle-sheets-filter

I've been stumped on this googlesheets problem and can't think of the best way to do this or I'm overthinking this.

Essentially if I have a list of values, I'd like to compare it to another list of values. And if they have the same values at any point, I'd like it to remove it from an updated list that's outputted into a new cell.

Eg.
Example

In my example, I have a list of fruits in column A, a different list of fruits in column B, and the difference between column A and column B is what I'd like to get in column C. Thanks for the help!

Edit:
Ah, I'ved tried using the unique function, but it would give me the unique values of both the lists. I've also tried using the filter function, but I'm not exactly sure how the criterion for the filter would work with a list of values

Best Answer

Here's a way to achieve this 

=FILTER(B1:B10,
     iferror(ArrayFormula(
VLOOKUP(B1:B10,$A$1:$A$10,1,false))
,true))

EDIT : If you have a lot of values this is maybe a better solution : 

=FILTER(B1:B10,ArrayFormula(COUNTIF($A$1:$A$10,B1:B10)-1))

enter image description here

Breakdown of the formula VLOOKUP:

Vlookup to see if there's a match between values in B and A. It returns the value when true, and an error when false.
Iferror to replace the error with true.
Filter to keep only the relevant answers (when there's a true statement).

Formula COUNTIF :

Countif there's the value B in A : return 0 = (False) or 1 = (TRUE).
-1 : now the False values are true and true values are false.
/!\ this doesn't work when column A contains duplicate : use UNIQUE to futur-proof the formula.

Related Topic