Search code examples
google-sheetsmergegoogle-sheets-formulaarray-formulas

Google Sheets Completely exclude duplicates

This formula:

=FILTER(SORT(UNIQUE(flatten(C3:H14));1;1); SORT(UNIQUE(flatten(C3:H14));1;1)<>"")

Merges all values in C3:H14 into a single column then removes duplicates and sorts the result in ascending order.

It is possible to compare the result with a second list and then leave only the values that never repeat?

Like, First Range

   A      B       
Orange  Grapes  
Apple   Carrot

Flattened Range (A + B)

   C
Apple
Carrot
Grapes
Orange

Comparison range

   D  
Apple  
Banana  
Carrot
Grapes
Orange  
Peach

So the final result will be (D - C):

   E     
Banana
Peach

Although using UNIQUE function is possible to remove duplicates while keeping one copy of each value.

How can we approach this situation to leave only values that never repeat? Also, it would be possible to use a Named Range as comparison range? Thanks for the attention.

Solution

  • You mention

    So the final result will be (D - C)

    And further down

    How can we approach this situation to leave only values that never repeat?

    Please use following formula

    =FILTER(B2:B,ISERROR(MATCH(B2:B,A2:A,0)))

    enter image description here

    As for the named range please see more here

    EDIT (copying @Mateo's great comment)

    To provide a bit more of information on how this function works.

    • First it looks for exact matches between your two columns returning the relative position of these matches.
    • If there are any values that don't match, they will be returned as an error #N/A which are caught by the function ISERROR.
    • Finally, to get these "unmatches" you filter your biggest range column B with these values that did not match and you return them.

    Functions used: