Search code examples
google-sheetssplitgoogle-sheets-formulauniquetranspose

Return unique list that excludes another list

I want to return a set of unique values with categorical variables across multiple columns that that excludes values from another list.

Image link since I can't embed images in my question yet

=UNIQUE(QUERY({J2:J,K2:K},"select * where Col1 is not null"))

This properly returns the unique set of values with categorical variables but I can't seem to figure out how to exclude values from another list. 

=UNIQUE(FILTER(J2:K,ISNA(MATCH(M2:N,J2:K,0))))

I've tried using this but it returns 

#VALUE! FILTER range must be a single row or a single column.

Any help would be appreciated!

Solution 
  • =ARRAYFORMULA(IFERROR(SPLIT(FILTER(
 TRANSPOSE(QUERY(TRANSPOSE(UNIQUE(A2:B)), , 999^99)), NOT(COUNTIF(
 TRANSPOSE(QUERY(TRANSPOSE(       D2:E) , , 999^99)), 
 TRANSPOSE(QUERY(TRANSPOSE(UNIQUE(A2:B)), , 999^99))))), " ")))

    0

    to account for items with spaces:

    =ARRAYFORMULA(SUBSTITUTE(IFERROR(SPLIT(FILTER(
 TRANSPOSE(QUERY(TRANSPOSE(UNIQUE(SUBSTITUTE(A2:B," ","♦"))),,999^99)), NOT(COUNTIF(
 TRANSPOSE(QUERY(TRANSPOSE(       SUBSTITUTE(D2:E," ","♦")) ,,999^99)), 
 TRANSPOSE(QUERY(TRANSPOSE(UNIQUE(SUBSTITUTE(A2:B," ","♦"))),,999^99)))))," ")),"♦"," "))

    0