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:
(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:
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?
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,))))