Get the missing data

[SandsB]

There's always some VBA I'm not smart enough to do.
So I have 2 lists each with the name of a record owner in column A. List1 is all the records I already know belong to Bob. The number of rows varies each day. I want to get the contents of each row in List2 (# of rows also varies) that shows Bob in column A if that record number isn't already in List1 and I need those additional rows to be placed under whatever is the bottom row in today's version of List1. In the example below, I have 4 records for Bob in List1 so I need to add the rows for Bob Record8 and Bob Record11 starting in row 5 of List1. I don't need to edit List2 at all and I'm not concerned with anyone other than Bob.

List1
Bob Record1
Bob Record2
Bob Record5
Bob Record7


List2
Bob Record1
Bob Record2
Joe Record3
Bob Record5
Mary Record6
Bob Record7
Bob Record8
Ed Record9
Mary Record10
Bob Record 11

 

[alansidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Merge = Table.NestedJoin(Source, {"List1","Column1"}, Source2, {"List2","Column1"}, "Table2", JoinKind.FullOuter),
    ExpandTable = Table.ExpandTableColumn(Merge, "Table2", {"List2", "Column1"}, {"List2", "Column1.1"}),
    FilterRows = Table.SelectRows(ExpandTable, each ([List2] = "Bob")),
    RemoveColumns = Table.SelectColumns(FilterRows,{"List2", "Column1.1"})
in
    RemoveColumns

Book4
	A	B	C	D	E
1	List1	Column1		List2	Column1.1
2	Bob	Record1		Bob	Record1
3	Bob	Record2		Bob	Record2
4	Bob	Record5		Bob	Record5
5	Bob	Record7		Bob	Record7
6				Bob	Record8
7				Bob	Record11
8	List2	Column1
9	Bob	Record1
10	Bob	Record2
11	Joe	Record3
12	Bob	Record5
13	Mary	Record6
14	Bob	Record7
15	Bob	Record8
16	Ed	Record9
17	Mary	Record10
18	Bob	Record11
Sheet1

 

[SandsB]

Thanks, Power Query looks interesting and I'll play with it later. This doesn't quite do what I'm looking for, though.