Replace contents of cell with adjacent cell based on certain text

[mozza90]

Hi,

I have a sheet which lists data like the below:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Column E[/TD]
[TD="align: center"]Column F[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Other[/TD]
[TD]Melon[/TD]
[/TR]
[TR]
[TD]Other[/TD]
[TD]Lime[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


If column E contains "Other" then the answer is found in column F. What's the best way to look down column E, find "Other", and replace other with the contents of the adjacent cell e.g. Other becomes Melon, and the second instance of Other becomes Lime?

 

[Joe4]

With VBA code, like this:

Sub MyReplace()

    Dim lastRow As Long
    Dim cell As Range
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column E
    lastRow = Cells(Rows.Count, "E").End(xlUp).Row
    
'   Loop through column E
    For Each cell In Range("E1:E" & lastRow)
        If cell = "Other" Then
            cell = cell.Offset(0, 1)
        End If
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

 

[JackDanIce]

Use a spare column and enter formula:

=IF(E2="Other",F2,E2)

Drag that formula down to the last row, then copy and paste values to your destination cell, then delete the spare column

 

[mozza90]

Thanks Joe4! so simple.

 

[Joe4]

You are welcome!