VBA Code for Compare two columns and extract unique values

[mralby]

I have two distinctive columns, COLUMN A (Customer List 1) & COLUMN D (Main Customer List)
Whenever I added a new entry in COLUMN A, run VBA script (Macro) to extract unique values from COLUMN A and add to the last empty cell to the COLUMN D?

Below is the image.

 

[alansidman]

Power Query.

Enter any new data in Column A. On the Data Tab, click Refresh All and Data will update in Column D. When you Close and Load, Select D1 as the destination

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Customer List1"}, {{"Master Customer List", each _, type table [Customer List1=number]}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Master Customer List"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Customer List1", "Master Customer List"}})
in
    #"Renamed Columns"