Forum Discussion

CyndiW124's avatar
CyndiW124
Copper Contributor
Apr 01, 2024

Change numbers in one column to names from another column

I have a monthly spreadsheet download that has a list of Driver ID numbers that I need to transform into Driver Names. You can see the Driver ID column has multiple cells with the same number. The list of ID numbers with the correlating names is in a different spreadsheet. For instance: ID 70771 needs to be replaced with the name Munoz, Brian. What formula will do this automatically?

 

Excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Apr 01, 2024

    CyndiW124 

    Sub driver_id()

    Dim drivernames As Object, driverid As Range
    Dim i As Long, scriptingKey As String
    Dim resultData
    
    Set drivernames = CreateObject("scripting.dictionary")
    Set driverid = Range("A2:B" & Cells(Rows.Count, 1).End(xlUp).Row)
    
    resultData = driverid.Value
    
    For i = LBound(resultData) To UBound(resultData)
        scriptingKey = resultData(i, 1)
        drivernames(scriptingKey) = resultData(i, 2)
    Next i
    
    Set driverid = Range("E2:E" & Cells(Rows.Count, "E").End(xlUp).Row)
    resultData = driverid.Value
    
    For i = LBound(resultData) To UBound(resultData)
        scriptingKey = resultData(i, 1)
        If drivernames.Exists(scriptingKey) Then _
            resultData(i, 1) = drivernames(scriptingKey)
    Next i
    
    driverid.Value = resultData
End Sub

    In the attached file you can click the button in cell G2 to run the macro that replaces the id's in column G with the corresponding values from column B.

Resources