Forum Discussion
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
=IF(ISNUMBER(MATCH(A2,$B$2:$B$8,0))=FALSE,A2,"")An alternative for older versions of excel could be this formula.
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).
- Thank you! Super easy formula and it worked!!!
- Amazing - thank you very much!
