Compare two columns and leave only the values that don't match-CodePudding

Question is pretty self explanatory. I have two columns (num1, num2) and I just want to return the values that do not match (and not the others):

So the ideal return is simply 2,3,4,5

Edited for clarity**

CodePudding user response：

use:

=FILTER(FLATTEN(A2:B), COUNTIFS(FLATTEN(A2:B), FLATTEN(A2:B))=1)

or if you want it sorted:

=SORT(FILTER(FLATTEN(A2:B), COUNTIFS(FLATTEN(A2:B), FLATTEN(A2:B))=1))

or if you want it joint:

=TEXTJOIN(", ", 1, SORT(FILTER(FLATTEN(A2:B), COUNTIFS(FLATTEN(A2:B), FLATTEN(A2:B))=1)))

CodePudding user response：

You could also use the optional [exactly once] argument of UNIQUE to achieve the same thing in this particular context:

=unique(flatten(A2:B),,1)

This can then be sorted/joined if required.