How to use filter function to find items in one list (including duplicates) that are not or are partially present in another list.

Question

I would like to figure out how to use the filter [removed_js] to find items that are in one list but not in another list. The catch is that this function has to be able to detect items that show up in one list 2 or 3 times but only show up once or twice in the other list. For example, if list A has the numbers 1, 3, 5, 5, 5, 6, 6, 8, 9, 9, 10 and list B has the numbers 1, 5, 6, 6, 8, 9, 10, I need the filter function to return the list 3, 5, 5, 9. This will be used to filter out items that should not be showing up in one list or are showing up too many times in one list so I can reconcile the two lists quicker. The function I am currently using is =FILTER(A1:A2000,NOT(ISNUMBER(MATCH(A1:A2000,B1:B2000,0)))). This does a good job of showing my items in list A that do not appear in list B, but unfortunately does not show me items that show up 4 or 5 times in list A so long as it shows up even once in list B. Any ideas how to resolve this?

Answer 1

Hi,

In cell D2, enter this formula

=LET(s,BYROW(A2:A12,LAMBDA(x,COUNTIF(A2:x,x)))&A2:A12,c,BYROW(B2:B8,LAMBDA(x,COUNTIF(B2:x,x)))&B2:B8,FILTER(A2:A12,ISERROR(XLOOKUP(s,c,c))))

Hope this helps.

Answer 2

What is this "complement" formula and how do I use it? That's not something that shows up on my excel.

Answer 3

That's exactly what i'm trying to do. I don't have any experience with vba though

Answer 4

I'm going to have to admit that I haven't the slightest clue what I'm looking at with this formula but it worked like a charm. Thank you so much. You just saved me hours of work.

Answer 5

Since the reply given by Ashish Mathur has helped you, then you should mark this reply as Answer.

In this way the other users of this forum can also benefit.