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

In other words, you want to subtract ListB from ListA and show the remaining items.

Using VBA, easy. Using a formula....?

Andreas.

Answer 3

I don't really know what the difference between string and value is. For context, I'm trying to complete to reconcile a company's books with their bank statements. This company has 1000s of transactions so it's tedious work having to go through each transaction to find what's missing and what's duplicated. The two lists will always be hard values though so is this string issue something I have to worry about?