CODEWITHSUNDEEP
google-sheetsfiltercountduplicatesflatten
clucko87
clucko87

Reputation: 89

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

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):

enter image description here

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

Edited for clarity**

Upvotes: 1

Views: 2528

Answers (2)

The God of Biscuits
The God of Biscuits

Reputation: 3187

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.

Upvotes: 0

player0
player0

Reputation: 1

use:

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

enter image description here

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)))

Upvotes: 1

Related Questions