I have a list (column A) of non-unique text values. Items from that list are put into column B (via dropdown data validation). Any items from column A that are missing in column B are shown in column C using this formula in C1:

=iferror(filter(A1:A, isna(match(A1:A, B1:B,0))))

Column A is technically sorted alphabetically, but it doesn't need to be, and I'm not sure that matters. Again, items occasionally repeat in column A. For every time a text value such as "x" appears in column A, but not in column B, column C has been showing every iteration of "x" in its list. But when "x" appears in column B just once, all iterations in column C vanish.

I'd like for the formula to treat every value in column A as unique, even when the strings are identical. If there are two iterations of "y" in column A, but only one "y" in column B, then C should have one remaining "y" left over.

I'm starting to think filter and match are the wrong functions to use for this.


1 Answer 1


See Test sheet

Main idea is to label data in column A and B with sequential number of occurrence for each position (e.g if item "Test" is repeated 3 times in column A - we need Test - 1, Test - 2, Test - 3). That's main reason why formula is so huge.

Once we get virtual data for both columns, i use similar logic with FILTER, ISNA and VLOOKUP to trace non-matching values.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.