SOLVED

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

Copper Contributor

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

Hi @Chloe_Apsey 

 

Assuming Excel 2021 or 365

 

_Screenshot.png

 

in E2:

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

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

@Chloe_Apsey 

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

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

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

@Gr8lif Similarly:

Sample.png

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

 

1 best response

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

Hi @Chloe_Apsey 

 

Assuming Excel 2021 or 365

 

_Screenshot.png

 

in E2:

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

View solution in original post