Multiple find and replace

[marcidee]

I have a very long spreadsheet that contains company names (they repeat themselves numerous times) (presently located in Column E from row 2) that I would like to replace with a coded name (ie Company A) - I would like to create a list of all the names and then replace with the coded names.

Is there a simple quick way of doing this (I can create a table / list of all the names and replacement names).

So eg i can create a list

Arsenal replace with Company A
Chelsea replace with Company B
Fulham replace with Company C .... and so on

Thank you for your help.

Marc

 

[Joe4]

I would probably approach it this way.
I would have a two column list of all your replacements. Then have VBA look through the lists, and make all the replacements in column E.
The code might look something like this:

Sub MyReplaceMacro()

    Dim lrE As Long
    Dim rngE As Range
    Dim lrLU As Long
    Dim r As Long
    Dim c As Long
    Dim lu As String
    Dim rep As String
    
    Application.ScreenUpdating = False
    
'   Designate which column the lookup values are found in (column "A"=1, etc)
    c = 1
    
'   Find last row with data in your lookup list
    lrLU = Cells(Rows.Count, c).End(xlUp).Row
    
'   Find last row in column E with data
    lrE = Cells(Rows.Count, "E").End(xlUp).Row
'   Set range to apply to
    Set rngE = Range("E2:E" & lrE)
    
'   Loop through lookup table, starting in row 2
    For r = 2 To lrLU
'       Get values to lookup and replace from current row
        lu = Cells(r, c)
        rep = Cells(r, c + 1)
'       Do find and replace in data
        On Error Resume Next
        rngE.Replace What:=lu, Replacement:=rep, LookAt:=xlWhole
        On Error GoTo 0
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

I think the only thing you should really have to replace is the value of "c", which indicates the left-most column of your two column lookup/replace list.

 

[marcidee]

Thank you very much for your help - please can you tell me where the two column list of replacements would be placed - based on your code above - assuming I have two columns 1st with the name and the second with the replacement?

Marc

 

[Joe4]

Thank you very much for your help - please can you tell me where the two column list of replacements would be placed - based on your code above - assuming I have two columns 1st with the name and the second with the replacement?

It can be anywhere.

For this example, I am assuming it is in columns A and B, and starts on row 2.

Note that the "c" variable is the column number where it starts ("A"=1, "B"=2, "C"=3, etc).

'   Designate which column the lookup values are found in (column "A"=1, etc)
    c = 1

So you should adjust this value to match the column number of the left column in your list.

Then the starting value for the "r" variable is the row number the first entry in that list begins on (I assumed 2 in this example):

'   Loop through lookup table, starting in row 2
    For r = 2 To lrLU

So you can change that "2" to match whatever row number the value in your list begins on.

Note that I have added lots of comments to document the code so you can follow along to see what is happening at each step along the way, and hopefully be able to maintain it yourself going forward.

 

[marcidee]

Brilliant - thank you very much

 

[Joe4]

You are welcome.