Extract Items that are in List1 but not in List2 - Excel and Power Query

There are numerous methods in Excel and Power Query to accomplish this task.

In this post, we will discuss few of them.

Not in List2.xlsx

Download XLSX • 27KB

Excel:

Method-1:

=FILTER(Table1[List1],ISNA(XMATCH(Table1[List1],Table2[List2])))

Method-2:

=FILTER(Table1[List1],1-BYROW(--(Table1[List1]=TOROW(Table2[List2])),SUM))

Method-3:

=FILTER(Table1[List1],1-COUNTIF(Table2[List2],Table1[List1]))

Method-4:

=FILTER(Table1[List1],1-MAP(Table1[List1],LAMBDA(x,COUNTIF(Table2[List2],x))))

Method-5:

=TOCOL(SCAN("",Table1[List1],LAMBDA(a,v,IFS(1-COUNTIF(Table2[List2],v),v))),3)

Method-6: Proposed by Vijay Kumar.

=FILTER(A3:A12,NOT(COUNTIF(C3:C9,A3:A12)))

Method-7: Proposed by Rick Rothstein.

=UNIQUE(VSTACK(Table1[List1],Table2[List2],Table2[List2]),,1)

Method-8: Proposed by Abhishek Kumar Maurya.

=LET(a,A3:A12,b,C3:C12,x,IF(IFNA(VLOOKUP(a,b,1,FALSE),"")=a,"",a),FILTER(x,x<>""))

Power Query:

Method-9:

let

Table1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

Table2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],

Result = Table.FromList(List.Difference(Table1[List1], Table2[List2]),null, {"Name"})

in

Result

Method-10:

let

Source = each Excel.CurrentWorkbook(){[Name=_]}[Content],

Result = Table.NestedJoin( Source("Table1"), {"List1"},Source("Table2"), {"List2"}, "Name", JoinKind.LeftAnti)[[List1]]

in

Result

Method-11:

let

Table1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

Table2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],

Result = Table.FromList(List.RemoveMatchingItems(Table1[List1], Table2[List2]), null, {"Name"})

in

Result

Method-12: Proposed by Abdallah Ally

let

Table1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

Table2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],

Result = Table.FromList(List.Select(Table1[List1], each not List.Contains(Table2[List2], _)), null, {"Name"})

in

Result