Comparing two lists of Email Address and names and pulling the non-matching data to a new list

[Chloe_Apsey]

Hello,

I currently have an original list of participant's email who I contacted to take part in a study. I now have a second list of the emails of the participants who have taken part in the study after I have contacted them.

I want to be able to compare these two list and get an output of all the participants who have not yet taken part in the study so I can contact them with a reminder - does anybody have any advice on how to do this?

Thank you

[#2 · Accepted solution]

Hi @Chloe_Apsey 

Assuming Excel 2021 or 365

in E2:

=FILTER(A2:A5, ISNA(XMATCH(A2:A5,C2:C3)))

[#3]

Amazing - thank you very much!

[#4]

@Chloe_Apsey 

Let's say the original list of email addresses is on Sheet1 in E2:E1000, and the list of participating email addresses is on Sheet2 in E2:E500.

If you have Microsoft 365 or Office 2021, enter the following formula in the cell where you want the output to start. There should be sufficient empty cells below it.

=FILTER('Sheet1'!E2:E1000,ISERROR(MATCH('Sheet1'!E2:E1000,'Sheet2'!E2:E500,0)),"None")

If you have an older version, enter the following formula in a cell in row 2 where you want the output to start, and confirm it with Ctrl+Shift+Enter to turn it into an array formula:

=IFERROR(INDEX('Sheet1'!$E$2:$E$1000,SMALL(IF(ISERROR(MATCH('Sheet1'!$E$2:$E$1000,'Sheet2'!$E$2:$E$500,0)),MATCH(ROW('Sheet1'!$E$2:$E$1000),ROW('Sheet1'!$E$2:$E$1000)),""),ROWS($E$2:$E2))),"")

Fill down until the formula returns a blank (or even further down, that doesn't matter).

[#5]

@Chloe_Apsey 

=IF(ISNUMBER(MATCH(A2,$B$2:$B$8,0))=FALSE,A2,"")

An alternative for older versions of excel could be this formula. 

[#6]

Thank you! Super easy formula and it worked!!!

[#7]

@Gr8lif Similarly:

=FILTER(A2:A5, COUNTIF(C2:C3,A2:A5) = 0)