A macro that would look up the a column in a worksheet1 to populate the column of a worksheet2

Private Sub Update_Click()
Dim Ws As Worksheet
Dim LR As Long
Application.ScreenUpdating = False
Call MakeUniqueList
Set Ws = Worksheets("Sheet1") 'Change the sheet name as required
LR = Ws.Range("A" & Rows.Count).End(xlUp).Row 'Change the column, which has the maximum number of rows filled
Ws.Range("B1:B" & LR).FormulaR1C1 = "=IFERROR(VLOOKUP(RC1,Sheet2!C1:C2,2,0),"""")"
Ws.Range("B1:B" & LR).Value = Ws.Range("B1:B" & LR).Value
Ws.Range("B1").Select
Application.ScreenUpdating = True
End Sub
Sub MakeUniqueList()
    Dim Ws1 As Worksheet, Ws2 As Worksheet
    Dim LR As Long
    Dim vaData As Variant
    Dim colUnique As Collection
    Dim aOutput() As Variant
    Dim i As Long
    
    Set Ws1 = Worksheets("Sheet2")
    Set Ws2 = Worksheets("Sheet1")
    LR = Ws1.Range("A" & Rows.Count).End(xlUp).Row
    
    'Put the data in an array
    vaData = Ws1.Range("A1:A" & LR).Value

    'Create a new collection
    Set colUnique = New Collection

    'Loop through the data
    For i = LBound(vaData, 1) To UBound(vaData, 1)
        'Collections can't have duplicate keys, so try to
        'add each item to the collection ignoring errors.
        'Only unique items will be added
        On Error Resume Next
            colUnique.Add vaData(i, 1), CStr(vaData(i, 1))
        On Error GoTo 0
    Next i

    'size an array to write out to the sheet
    ReDim aOutput(1 To colUnique.Count, 1 To 1)

    'Loop through the collection and fill the output array
    For i = 1 To colUnique.Count
        aOutput(i, 1) = colUnique.Item(i)
    Next i

    'Write the unique values to column A
    Ws2.Range("A1").Resize(UBound(aOutput, 1), UBound(aOutput, 2)).Value = aOutput

End Sub

Select allOpen in new window