google-sheetsgoogle-sheets-formulaspreadsheetarray-formulas

Google Sheets - Difference of Two Columns With Duplicate Values

I am currently having an issue where I need to find the difference between two columns of text values (AT and AV) while maintaining any duplicates. A sample picture of what the two columns may look like is provided below:

Sample Picture

(Column AV will never contain more of a specific value than AT. For example, if AT contains 3 of "a", AV will contain three or less of "a").

Example inputs and outputs:

  1. AT contains "a", "a", "a". AV contains "a". Output should be "a", "a".
  2. AT contains "a", "a", "b", "c". AV contains "a", "c". Output should be "a", "b".
  3. AT contains "a", "b", "c". AV contains "a", "b", "c". Output should be "".

The current formula I attempted to use is:

=QUERY(FILTER(AT3:AT,ISERROR(MATCH(AT3:AT,AV3:AV,false))),"select Col1 where Col1 is not null",0)

(AT may contain null values, hence the query). The issue is, this also removes duplicates. For the three examples listed above, the outputs would be 1. "", 2. "b", 3. "", of which 1. and 2. are incorrect. Is there a function I can use to solve this problem?

Solution

  • You can label each value with its current count and take the count into account when performing the filtering.

    =LET(L,LAMBDA(x,x&COUNTIFS(x,x,ROW(x),"<="&ROW(x))),FILTER(AT3:AT,ISNA(VLOOKUP(L(AT3:AT),L(AV3:AV),1,))))