How to subtract two sets of values in Google Spreadsheets?

Question

I have two sets of values in A:A and B:B. How to make a set difference between them (preferably using a formula), i.e. get those values from A:A that do not exist in B:B?

Answer 1

Such a formula does this, and is quite fast:

=filter(A:A,countif(B:B,A:A)=0)

The way this works is the arrayformula behavior in filter's second argument. countif accepts a range and a single criteria or value. So each element of A:A is passed to countif one at a time, counting the occurrence of said element within B:B.

Answer 2

Here’s a spreadsheet showing how set operations can be done easily:

Set Operations in Google Sheets

• Union is ={setA; setB}
• Difference (setA-setB) is =filter(setA, iserror(MATCH(setA, setB, false)))
• Intersection is =filter(setA; MATCH(setA, setB, false))

Explanation:

setA and setB can be named ranges or you can use normal range notation. Named ranges just make this clearer.

Union is just a new range made by juxtaposing both ranges.

Intersection (next easiest) depends on looking for indices of setA in setB where they exits, and filtering setA by that.

Difference is similar, but filters setA to pick out only members where finding the index in setB is not found.

Extra Credit:

Union with duplicate elimination is just setA + (setB-setA), so by the above:

={setA;filter(setB, iserror(MATCH(setB,setA,false)))}

Answer 3

Try this formula:

=FILTER(A:A,REGEXMATCH(A:A&" ",JOIN("|",FILTER(B:B,B:B<>"")))=FALSE)