Replace/Substitute values based on VLOOKUP value

[TTXS]

Hello, I have an excel table that has a list of customer names. However, because it was inputted by multiple users, the naming formats are not consistent. For example, customer 1 may be entered as "customer (1)", or "CUST 1", or other variations. I want to create a new sheet with exact same data/format, but customer name have been cleaned up using VLOOKUP of the Master customer table. Ideally, I would like to use a VBA for it. Thank you.

Let's call this table Sheet 1 - that's the one I want to duplicate but with cleaned up customer name.

This is my Master customer table.

 

[kevin9999]

There's any number of ways you could do this - here's just one. Change the sheet names & range references to suit your customer names table & "Master" customer table.

Option Explicit
Sub Correct_Customers()
    Dim a, b, i As Long, j As Long
    a = Sheets("Sheet1").Range("G2:G" & Sheets("Sheet1").Cells(Rows.Count, "G").End(xlUp).Row)
    b = Sheets("Sheet2").Range("A2:B" & Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row)
    
    For i = 1 To UBound(a, 1)
        For j = 1 To UBound(b, 1)
            If a(i, 1) = b(j, 1) Then
                a(i, 1) = b(j, 2)
                Exit For
            End If
        Next j
    Next i
    Sheets("Sheet1").Range("G2").Resize(UBound(a, 1)).Value = a
End Sub

 

[kvsrinivasamurthy]

Try. This will correct the names in Master table. Others will be left as it is.

Sub CorrectNames()
Dim M, X&
M = Evaluate("transpose(MMULT((G2:G5=TRANSPOSE(master!A2:A5))*TRANSPOSE((ROW(master!A2:A5))-1),1*(ROW(master!A2:A5)>0)))")
For Each cel In Range("g2:g5")
X = X + 1
If M(X) <> 0 Then cel.Value = WorksheetFunction.Index(Sheets("Master").Range("B2:B5"), M(X), 1)
Next cel
End Sub

 

[Peter_SSs]

Here is a method that should fix them all at once without looping through the individual rows.
I have assumed the master list is in columns A:B of worksheet 'Master'. Just edit that in the code if the name is different.

Sub Correct_Names()
  Dim rng As Range
  
  Set rng = Sheets("Sheet1").Range("G2", Sheets("Sheet1").Range("G" & Rows.Count).End(xlUp))
  rng.Value = rng.Worksheet.Evaluate(Replace("if(#="""","""",ifna(vlookup(T(IF({1},#)),'Master'!A:B,2,0),#))", "#", rng.Address))
End Sub