SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-3385351%22%20slang%3D%22en-US%22%3EComparing%20two%20lists%20of%20Email%20Address%20and%20names%20and%20pulling%20the%20non-matching%20data%20to%20a%20new%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3385351%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20currently%20have%20an%20original%20list%20of%20participant's%20email%20who%20I%20contacted%20to%20take%20part%20in%20a%20study.%20I%20now%20have%20a%20second%20list%20of%20the%20emails%20of%20the%20participants%20who%20have%20taken%20part%20in%20the%20study%20after%20I%20have%20contacted%20them.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20be%20able%20to%20compare%20these%20two%20list%20and%20get%20an%20output%20of%20all%20the%20participants%20who%20have%20not%20yet%20taken%20part%20in%20the%20study%20so%20I%20can%20contact%20them%20with%20a%20reminder%20-%20does%20anybody%20have%20any%20advice%20on%20how%20to%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3385351%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3385527%22%20slang%3D%22en-US%22%3ERe%3A%20Comparing%20two%20lists%20of%20Email%20Address%20and%20names%20and%20pulling%20the%20non-matching%20data%20to%20a%20new%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3385527%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1391399%22%20target%3D%22_blank%22%3E%40Chloe_Apsey%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20Excel%202021%20or%20365%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22_Screenshot.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F372248iD9856F57212A918D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22_Screenshot.png%22%20alt%3D%22_Screenshot.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ein%26nbsp%3B%3CSTRONG%3EE2%3C%2FSTRONG%3E%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DFILTER(A2%3AA5%2C%20ISNA(XMATCH(A2%3AA5%2CC2%3AC3)))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3385596%22%20slang%3D%22en-US%22%3ERe%3A%20Comparing%20two%20lists%20of%20Email%20Address%20and%20names%20and%20pulling%20the%20non-matching%20data%20to%20a%20new%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3385596%22%20slang%3D%22en-US%22%3EAmazing%20-%20thank%20you%20very%20much!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3385635%22%20slang%3D%22en-US%22%3ERe%3A%20Comparing%20two%20lists%20of%20Email%20Address%20and%20names%20and%20pulling%20the%20non-matching%20data%20to%20a%20new%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3385635%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1391399%22%20target%3D%22_blank%22%3E%40Chloe_Apsey%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20original%20list%20of%20email%20addresses%20is%20on%20Sheet1%20in%20E2%3AE1000%2C%20and%20the%20list%20of%20participating%20email%20addresses%20is%20on%20Sheet2%20in%20E2%3AE500.%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20Microsoft%20365%20or%20Office%202021%2C%20enter%20the%20following%20formula%20in%20the%20cell%20where%20you%20want%20the%20output%20to%20start.%20There%20should%20be%20sufficient%20empty%20cells%20below%20it.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DFILTER('Sheet1'!E2%3AE1000%2CISERROR(MATCH('Sheet1'!E2%3AE1000%2C'Sheet2'!E2%3AE500%2C0))%2C%22None%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20an%20older%20version%2C%20enter%20the%20following%20formula%20in%20a%20cell%20in%20row%202%20where%20you%20want%20the%20output%20to%20start%2C%20and%20confirm%20it%20with%20Ctrl%2BShift%2BEnter%20to%20turn%20it%20into%20an%20array%20formula%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFERROR(INDEX('Sheet1'!%24E%242%3A%24E%241000%2CSMALL(IF(ISERROR(MATCH('Sheet1'!%24E%242%3A%24E%241000%2C'Sheet2'!%24E%242%3A%24E%24500%2C0))%2CMATCH(ROW('Sheet1'!%24E%242%3A%24E%241000)%2CROW('Sheet1'!%24E%242%3A%24E%241000))%2C%22%22)%2CROWS(%24E%242%3A%24E2)))%2C%22%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down%20until%20the%20formula%20returns%20a%20blank%20(or%20even%20further%20down%2C%20that%20doesn't%20matter).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3385750%22%20slang%3D%22en-US%22%3ERe%3A%20Comparing%20two%20lists%20of%20Email%20Address%20and%20names%20and%20pulling%20the%20non-matching%20data%20to%20a%20new%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3385750%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1391399%22%20target%3D%22_blank%22%3E%40Chloe_Apsey%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(ISNUMBER(MATCH(A2%2C%24B%242%3A%24B%248%2C0))%3DFALSE%2CA2%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EAn%20alternative%20for%20older%20versions%20of%20excel%20could%20be%20this%20formula.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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

4 Replies
best response confirmed by Chloe_Apsey (New 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.