Forum Discussion
MarkolaKrai
Mar 07, 2023Copper Contributor
Set theory operations with dynamic arrays
I wonder if any of you have figured this out already (and i would like to have feedback about what i have done so far), i have been playing with columns of data and creating easy set theory operation...
Greg_Hullender
Apr 06, 2025Copper Contributor
Actually, I came up with a reasonably elegant way to do n-ary union and intersection where the input is an array of thunks, each thunk representing a set. This is union:
=LET(_l, LAMBDA(x,LAMBDA(x)),
sets, VSTACK(_l(B4:B11), _l(C4:C9), _l(D4:D12)),
UNIQUE(DROP(REDUCE(0,sets, LAMBDA(s,x,VSTACK(s,x()))),1)))
Lines 1 and 2 just set up the simulated input. This really does nothing but stack all the sets together and run unique on the whole. Intersection is much more interesting:
=LET(_l, LAMBDA(x, LAMBDA(x)),
sets, VSTACK(_l(B4:B11), _l(C4:C9), _l(D4:D12)),
union, UNIQUE(DROP(REDUCE(0, sets, LAMBDA(s,x, VSTACK(s, x()))), 1)),
not_x, UNIQUE(DROP(REDUCE(0, sets, LAMBDA(s,x, VSTACK(s, UNIQUE(VSTACK(x(), union), , 1)))), 1)),
UNIQUE(VSTACK(union,not_x), , 1))
This takes advantage of De Morgan's Law. In this case, the set complement is just the set subtracted from the union. (We don't need to double the set being subtracted since it's entirely contained in the union.) So we compute the union as before, then we compute the union of the complements of all the sets. Finally we take the complement of that (subtract it from the union) to get our answer.