CReplace value in the column if it matches with another column.

[topi1]

I have a following code that helps me clean cells in the column C for double spacing etc. I can add more rows in the vba to add more replacements, eg replace "bball" with "basketball". However, it is not efficient if I want to replace more than few strings. I want to have a code where if a cell value in the column C matches with a cell value in the column M, say cell M2, then the value in the cell in the column C is replaced with the value in N2. I can use vlookup or Index and MATCH formula for C lookup in the M:N range. However, I was wondering if there is vba that can do that. Thank you.

Sub Clean_ColumnC()
    Worksheets("Clean").Range("C1:C999").Replace "  ", ""
    Worksheets("Clean").Range("C1:C999").Replace " .", "."
    Worksheets("Clean").Range("C1:C999").Replace "..", "."

    Dim Arr, i As Long
    Arr = Worksheets("Clean").Range("C1:C999")
    For i = 1 To UBound(Arr, 1)
        Arr(i, 1) = Application.Clean(Arr(i, 1))
    Next i
    Worksheets("Clean").Range("C1:C999").Value = Arr
    
End Sub

 

[topi1]

Google sheets I don't know. Pl upload excel file.

@kvsrinivasamurthy Here is the link to download .xlsm file. ty.

15.9 KB file on MEGA

mega.nz

 

[kvsrinivasamurthy]

I have corrected the code.

Sub Clean_ColumnC_K()
    Worksheets("Clean").Range("C1:C999").Replace "  ", ""
    Worksheets("Clean").Range("C1:C999").Replace " .", "."
    Worksheets("Clean").Range("C1:C999").Replace "..", "."
    
     Dim Frng As Range

    Dim Arr, i As Long
  
    Arr = Worksheets("Clean").Range("C1:C999")
    For i = 1 To UBound(Arr, 1)
        Arr(i, 1) = Application.Clean(Arr(i, 1))
        Set Frng = Worksheets("Clean").Range("M:M").Find(What:=Arr(i, 1), After:=Range("M1"), LookIn:=xlFormulas, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
        , SearchFormat:=False)
        If Not Frng Is Nothing Then Arr(i, 1) = Range("N" & Frng.Row)
 Next i
    Worksheets("Clean").Range("C1:C999").Value = Arr
    
End Sub

 

[Fluff]

@topi1 I have unmarked post#3 as the solution as it obviously not one. In future if you realise that something is not working correctly please un-mark the solution. Thanks

 

[topi1]

@topi1 I have unmarked post#3 as the solution as it obviously not one. In future if you realise that something is not working correctly please un-mark the solution. Thanks

Will do. Thank you for letting me know.

 

[topi1]

I have corrected the code.

Sub Clean_ColumnC_K()
    Worksheets("Clean").Range("C1:C999").Replace "  ", ""
    Worksheets("Clean").Range("C1:C999").Replace " .", "."
    Worksheets("Clean").Range("C1:C999").Replace "..", "."
   
     Dim Frng As Range

    Dim Arr, i As Long
 
    Arr = Worksheets("Clean").Range("C1:C999")
    For i = 1 To UBound(Arr, 1)
        Arr(i, 1) = Application.Clean(Arr(i, 1))
        Set Frng = Worksheets("Clean").Range("M:M").Find(What:=Arr(i, 1), After:=Range("M1"), LookIn:=xlFormulas, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
        , SearchFormat:=False)
        If Not Frng Is Nothing Then Arr(i, 1) = Range("N" & Frng.Row)
 Next i
    Worksheets("Clean").Range("C1:C999").Value = Arr
   
End Sub

Thank you so much. Will try this evening.

 

[Alex Blakenburg]

@topi1, I am not sure why you have chosen not to provide an XL2BB in Post #1 when you have used it in the past.
There has been a fair bit of wasted effort that is going into this by not having a visibility of the data and misunderstanding the nature of the lookup.
Both respondents have been under the impression that it was a 1 for 1 lookup not that the column C had a phrase or sentence.

Your latest sample only has one line it and can't be representative or you would not need the replace & clean lines of code that you are using.
We are looking for patterns and exceptions in the data and you can't tell that from one line.

It is also unclear as to whether the end resul for "It is made by GM." should be:
a) "GM" or
b) "It is made by General Motors."

I have gone with Option b, which mean that you don't need to use a loop.
I have also assumed that you won't have the situation of having WaterMelon & Melon, since if you replace Melon with XXX you would get WaterXXX and XXX repectively.
If this the case we will need to use a loop and be more precise.

Sub Clean_ColumnC()

    Dim wsData As Worksheet
    Dim rngData As Range, rngLookup As Range
    Dim arrData As Variant, arrLookup As Variant
    Dim i As Long
    
    Set wsData = Worksheets("Clean")
    With wsData
        Set rngData = .Range("C1", .Cells(Rows.Count, "C").End(xlUp))
        Set rngLookup = .Range("M1", .Cells(Rows.Count, "N").End(xlUp))
    End With
    arrData = rngData.Value
    arrLookup = rngLookup.Value

    arrData = Application.Substitute(arrData, "  ", "")
    arrData = Application.Substitute(arrData, " .", ".")
    arrData = Application.Substitute(arrData, " .", ".")
    arrData = Application.Clean(arrData)
    
    For i = 1 To UBound(arrLookup, 1)
        arrData = Application.Substitute(arrData, arrLookup(i, 1), arrLookup(i, 2))
    Next i
    rngData.Value = arrData
    
End Sub

In case it helps anyone else, the source data was

20240124 VBA Clean Replace Substitute topi1.xlsm
	C	D								L	M	N
1	It is made by GM.										GM	General Motors
2											VW	Volkswagen
3	It is made by GM.
4	It is made by GM.
5	It is made by VW.
6	It is made by GM.
Clean

 

[topi1]

@topi1, I am not sure why you have chosen not to provide an XL2BB in Post #1 when you have used it in the past.
There has been a fair bit of wasted effort that is going into this by not having a visibility of the data and misunderstanding the nature of the lookup.
Both respondents have been under the impression that it was a 1 for 1 lookup not that the column C had a phrase or sentence.

Your latest sample only has one line it and can't be representative or you would not need the replace & clean lines of code that you are using.
We are looking for patterns and exceptions in the data and you can't tell that from one line.

It is also unclear as to whether the end resul for "It is made by GM." should be:
a) "GM" or
b) "It is made by General Motors."

I have gone with Option b, which mean that you don't need to use a loop.
I have also assumed that you won't have the situation of having WaterMelon & Melon, since if you replace Melon with XXX you would get WaterXXX and XXX repectively.
If this the case we will need to use a loop and be more precise.

Sub Clean_ColumnC()

    Dim wsData As Worksheet
    Dim rngData As Range, rngLookup As Range
    Dim arrData As Variant, arrLookup As Variant
    Dim i As Long
   
    Set wsData = Worksheets("Clean")
    With wsData
        Set rngData = .Range("C1", .Cells(Rows.Count, "C").End(xlUp))
        Set rngLookup = .Range("M1", .Cells(Rows.Count, "N").End(xlUp))
    End With
    arrData = rngData.Value
    arrLookup = rngLookup.Value

    arrData = Application.Substitute(arrData, "  ", "")
    arrData = Application.Substitute(arrData, " .", ".")
    arrData = Application.Substitute(arrData, " .", ".")
    arrData = Application.Clean(arrData)
   
    For i = 1 To UBound(arrLookup, 1)
        arrData = Application.Substitute(arrData, arrLookup(i, 1), arrLookup(i, 2))
    Next i
    rngData.Value = arrData
   
End Sub

In case it helps anyone else, the source data was

20240124 VBA Clean Replace Substitute topi1.xlsm
	C	D								L	M	N
1	It is made by GM.										GM	General Motors
2											VW	Volkswagen
3	It is made by GM.
4	It is made by GM.
5	It is made by VW.
6	It is made by GM.
Clean

Thank you. I can't wait to try your code. As to your comment, I agree entirely. I could have done better, and I am sorry. I guess it evolved from a word to a sentence or para in the column C. Sorry to have wasted people's valuable time. Hope to do better in the future.

 

[Alex Blakenburg]

No worries. It happens quite a lot. You are ahead of the pack since you do seem to have XL2BB up and running.
Let me know if you are really after Option (a). I can't look at it until tonight my time though.

 

[topi1]

Thank you so much. Will try this evening.

@kvsrinivasamurthy I am sorry it worked with some strings but not with others. I am sorry I wasted your time. Thank you for trying. Alex's script is working so we don't need to try any further. TY.

 

[topi1]

@topi1, I am not sure why you have chosen not to provide an XL2BB in Post #1 when you have used it in the past.
There has been a fair bit of wasted effort that is going into this by not having a visibility of the data and misunderstanding the nature of the lookup.
Both respondents have been under the impression that it was a 1 for 1 lookup not that the column C had a phrase or sentence.

Your latest sample only has one line it and can't be representative or you would not need the replace & clean lines of code that you are using.
We are looking for patterns and exceptions in the data and you can't tell that from one line.

It is also unclear as to whether the end resul for "It is made by GM." should be:
a) "GM" or
b) "It is made by General Motors."

I have gone with Option b, which mean that you don't need to use a loop.
I have also assumed that you won't have the situation of having WaterMelon & Melon, since if you replace Melon with XXX you would get WaterXXX and XXX repectively.
If this the case we will need to use a loop and be more precise.

Sub Clean_ColumnC()

    Dim wsData As Worksheet
    Dim rngData As Range, rngLookup As Range
    Dim arrData As Variant, arrLookup As Variant
    Dim i As Long
  
    Set wsData = Worksheets("Clean")
    With wsData
        Set rngData = .Range("C1", .Cells(Rows.Count, "C").End(xlUp))
        Set rngLookup = .Range("M1", .Cells(Rows.Count, "N").End(xlUp))
    End With
    arrData = rngData.Value
    arrLookup = rngLookup.Value

    arrData = Application.Substitute(arrData, "  ", "")
    arrData = Application.Substitute(arrData, " .", ".")
    arrData = Application.Substitute(arrData, " .", ".")
    arrData = Application.Clean(arrData)
  
    For i = 1 To UBound(arrLookup, 1)
        arrData = Application.Substitute(arrData, arrLookup(i, 1), arrLookup(i, 2))
    Next i
    rngData.Value = arrData
  
End Sub

In case it helps anyone else, the source data was

20240124 VBA Clean Replace Substitute topi1.xlsm
	C	D								L	M	N
1	It is made by GM.										GM	General Motors
2											VW	Volkswagen
3	It is made by GM.
4	It is made by GM.
5	It is made by VW.
6	It is made by GM.
Clean

@ Alex Blankenburg

No worries. It happens quite a lot. You are ahead of the pack since you do seem to have XL2BB up and running.
Let me know if you are really after Option (a). I can't look at it until tonight my time though.

@Alex Blakenburg As you guessed, I wanted option B. The code worked like a charm. I am going to stress it by adding more stuff in the M and N columns but so far it has worked flawlessly. Thank you so much. Saved me. I was going to create a sheet and use CONCATENATE formula to help me keep expanding the code by adding lines. I won't have to do that anymore.