Forum Discussion
Difference Between two lists
How to find difference between two given lists?
We generally use VLookup and other lookup functions for finding the common entries from 2 lists. Here, I have given formula for finding the differences in the lists. in A column, we have recipients of the meeting invite and in B column we have the attendees list. We want to find out who were all not attended the meeting. Here I have used COUNTIF function for finding the attendees, keeping attendees list in the range and recipient list in the criteria. It returns the count as 0s and 1s; 0's for the not attended and 1's for the attendees. After that, using FILTER function, and checking if the COUNTIF function results is zero. Filter function return the not attended list.
2 Replies
Maybe you can use the LET function, which allows you to create a variable and use it within a formula.
=LET(not_attended, FILTER(A:A, COUNTIF(B:B, A:A) = 0), not_attended)
LET function is not tested, use at the moment Excel 2016.
Otherwise, if it has to be a formula, the example would have to be a Countif function in column C and then the filter function in column D. Something like that.
Column C: Formula: =COUNTIF(B:B, A2)
Column 😧 Formula: =FILTER(A:A, C:C = 0).
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark them as helpful and like it!
This will help all forum participants.
