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.
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.
