How can we perform common set operations union intersection minus in MS Excel

Question

For example, I have an xls where :

• column A has list of items with property A
• column B has list of items with property B

I need the following :

• column C which is A union B (unique items of both A & B)
• column D which is A intersection B (common items of A & B)
• column E which is A minus B (items in A but not in B)
• column F which is B minus A (items in B but not in A)

Set operations on a list of elements seem to be easy with SQL or Python. But how to do it in xls?

Note : It should be an automation with minimal copy-pastes and clicks. For example, I dont want to copy-paste A below B, then "eliminate duplicates" to get A union B.

Answer 1

The MATCH function can be used as follows:

MATCH(value, array, match type) returns the value's relative position in the array. Match type is greater than, less than, or equal to.

If #VALUE is returned by an Exact MATCH, it signifies the value isn't in the array.
If it returns a positive number, it indicates that the value is an array.
IFERROR was used to distinguish between #VALUE (set to 0) and positive value (set to 1 using IF condition)
=IFERROR(IF(MATCH(A2,$B$2:$B$16,0)>0,1,0),0) is a function.