Compare tables and return values that aren't in both tables
So I need regularly to compare lists of varying size and check whether there are new values in the new list compared to the old list and return them.
I tried to come up with a formula that runs lightly by checking first how much data there is and then only use those rows and then returns only the values from the new list that are not in the old one.
From now on, I only need to paste the data in the "New list" sheet and "Old list" sheet and it runs automatically.
Question: can anyone think of a more efficient way to do this?
=LET(
OldList,TAKE('Old list'!B:B,COUNTA('Old list'!B:B)),
NewList,TAKE('New list'!B:B,COUNTA('New list'!B:B)),
FILTERNEW,IF(FILTER(NewList, ISNA(MATCH(NewList, OldList, 0)),"")=0,"",FILTER(NewList, ISNA(MATCH(NewList, OldList, 0)),"")),
TAKE(FILTERNEW,COUNTA(FILTERNEW))
)
[link] [comments]
Want to read more?
Check out the full article on the original site