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

You are welcome.