Google spreadsheet get complements

Question

As the title says, how can I end up with cells that don't match in value, the difference/complement (set theory)?

I would prefer it with only FUNCTIONS and no script.

{1,2,3,4}\{1,3} = {2, 4}

or with letters

{a,b,c,d}\{a,c} = {b, d}

or with with space.

{xyz zyx, abc cba, qwe ewq}\{xyz zyx, qwe ewq} = {abc cba}

You need to chance the permissions on your sheet to allow anyone to view it — Aurielle Perlmann, Feb 18 '16 at 13:31
sorry, it is changed now. But what I have in the sheet is basically the same examples as I havecome with in here — KaZyKa, Feb 18 '16 at 14:18

score 3 · Answer 1 · answered Feb 18 '16 at 14:26

3

You can add one single formula at the top of your result column like this :

=arrayformula(if(B2:B=A2:A,,A2:A))

answered Feb 18 '16 at 14:26

Aurielle Perlmann

2

That works. If you don't want any blank spaces between the values, you can use `=filter(A2:A,A2:A<>B2:B)` instead – daniel Feb 18 '16 at 14:41

Max Makhrov · Accepted Answer · 2016-04-04T14:18:24.530

Having two arrays:

Array1  Array2
  1       1
  2       b
  b 
  4 
  c

you can get resulting array:

Result
  2
  4
  c

Try this formula:

=FILTER(A2:A6,ROUND(MMULT(ArrayFormula(--(A2:A6<>TRANSPOSE(B2:B4))),TRANSPOSE(SPLIT(REPT(1/ROWS(B2:B4)&"/",Rows(B2:B4)),"/"))),5)=1)

May be too complex for this task, could somebody edit it? Please, look at sample sheet

Edit

I've found more simple way to do this, using Regular Expressions:

=FILTER(A2:A6,REGEXMATCH(ARRAYFORMULA("'"&A2:A6),JOIN("|",B2:B3))=false)

2 Answers2