CODEWITHSUNDEEP
google-sheetsfiltergoogle-sheets-formulacountifgoogle-sheets-query
Albert Rodriguez
Albert Rodriguez

Reputation: 13

How to calculate symmetric difference in Google Spreadsheets

I have two sets of values in columns A:A and B:B. I want to calculate symmetric difference between these two sets; i.e. get those values from A:A that do not exist in B:B and values from B:B that do not exist in A:A.

See example of desired result

So far I am using this and it works:

=FILTER({filter(A2:A,countif(B2:B,A2:A)=0);filter(B2:B,countif(A2:A,C2:C)=0)},{filter(A2:A,countif(B2:B,A2:A)=0);filter(B2:B,countif(A2:A,B2:B)=0)}<>"")

Is there a more efficient way to accomplish this?

Upvotes: 1

Views: 343

Answers (1)

player0
player0

Reputation: 1

get those values from A:A that do not exist in B:B AND values from B:B that do not exist in A:A

=QUERY({
 FILTER(A:A, NOT(COUNTIF(B:B, A:A))); 
 FILTER(B:B, NOT(COUNTIF(A:A, B:B)))}, 
 "where Col1 is not null", 0)

0

Upvotes: 0

Related Questions