SOLVED

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

Copper Contributor

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

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

6 Replies
best response confirmed by Chloe_Apsey (Copper Contributor)
Solution

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

Assuming Excel 2021 or 365

in E2:

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

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

Amazing - thank you very much!

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

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

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

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

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

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

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

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

@Gr8lif Similarly:

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

1 best response

Accepted Solutions
best response confirmed by Chloe_Apsey (Copper Contributor)
Solution

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

Assuming Excel 2021 or 365

in E2:

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