How to subtract two sets of values in Google Spreadsheets?
Such a formula does this, and is quite fast:
=filter(A:A,countif(B:B,A:A)=0)
Try this formula:
=FILTER(A:A,REGEXMATCH(A:A&" ",JOIN("|",FILTER(B:B,B:B<>"")))=FALSE)
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)))}