1

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.

New contributor
Ellen Battenfield is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
0

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.

| improve this answer | |

Your Answer

Ellen Battenfield is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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