Create separate data list from column excluding data from another column

[288enzo]

First, thank you to all those that have been helping me out with my project. I believe this is my last question, and it's difficult to explain.

I have a list of emails in a column on sheet data, I have another list of emails in a column on sheet1. I'd like to create a fresh list of emails from data excluding any that match the list in sheet3. I sure hope someone understands that

The master list of email addresses

Book1.xlsx
	A
1
2
3
4	email1
5	email2
6	email3
7	email4
8	email5
9	email6
10	email7
11	email8
12	email9
13	email10
Sheet2

Those emails to exclude

Book1.xlsx
	A
1	Email
2	email2
3	email4
4	email6
5
6
7
8
9
10
11
Sheet1

Cell Formulas
Range	Formula
A2:A11	A2	=IFERROR(INDEX(Sheet2!$A$4:$A$9999,AGGREGATE(15,6,ROW(Sheet2!$A$4:$A$9999)-ROW(Sheet2!$A$3)/(Sheet2!$B$4:$B$9999="Yes"),ROW()-ROW($A$1))),"")

This is what the result should look like, although I had to do it manually.

Book1.xlsx
	A
1	Email List
2	email1
3	email3
4	email5
5	email7
6	email8
7	email9
8	email10
Sheet3

Thank you!

 

[Fluff]

You can use the same formula as you've got on Sheet1, just change the ="Yes" to <>"Yes"

 

[alansidman]

With Power Query, bring each table into the PQ editor. Merge the two tables via a left anti join.

let
    Source = Table.NestedJoin(Table1, {"Column1"}, Table2, {"Email"}, "Table2", JoinKind.LeftAnti),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Email"}, {"Table2.Email"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table2",{"Table2.Email"})
in
    #"Removed Columns"

Book8
	A	B	C	D	E
4	Column1		Email		Column1
5	email1		email2		email1
6	email2		email4		email3
7	email3		email6		email5
8	email4				email7
9	email5				email8
10	email6				email9
11	email7				email10
12	email8
13	email9
14	email10
Sheet1

 

[288enzo]

You can use the same formula as you've got on Sheet1, just change the ="Yes" to <>"Yes"

OMG, thank you! That worked perfectly. One formula to solve all my problems.

 

[Fluff]

You're welcome & thanks for the feedback.