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)

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)))}