An alternative is to again use Power Query and join the two tables with a Right Anti Join.
Here is the Mcode.
let
Source = Table.NestedJoin(Table2, {"First Name", "Middle Name", "Last Name"}, Table1, {"First Name", "Middle Name", "Last Name"}, "Table1", JoinKind.RightAnti),
#"Expanded Table1" = Table.ExpandTableColumn(Source, "Table1", {"Date", "Number", "Address", "First Name", "Middle Name", "Last Name"}, {"Table1.Date", "Table1.Number", "Table1.Address", "Table1.First Name", "Table1.Middle Name", "Table1.Last Name"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Table1",{"First Name", "Middle Name", "Last Name"})
in
#"Removed Columns"
Excel 2016 (Windows) 32 bit
|
A |
B |
C |
D |
E |
F |
1 |
Table1.Date |
Table1.Number |
Table1.Address |
Table1.First Name |
Table1.Middle Name |
Table1.Last Name |
2 |
1/1/2020 |
15 |
123 Street |
John |
Arthur |
Smith |
3 |
1/20/2020 |
4 |
123 Street |
Michael |
James |
Jones |
4 |
2/15/2020 |
9 |
123 Street |
Enda |
Mary |
Smith |
1. In Version 2010 and 2013, you need to download the add in from MS. Power Query will work in any machine either having the add in or 2016 and later versions. Click on the link in my signature block to learn more about PQ.
2. If this is something you wish to explore further, the book "M is for (Data) Monkey" by Ken Puls and Miguel Escobar is a great primer. This is a very powerful piece of Excel and will allow you to easily manipulate data without learning code.
Bookmarks