Matching two columns cell for cell

[Moonbeam111]

I basically am trying to have column A match Column B exactly. All values in both columns are alphabetically organized but Column B has some extra values I don't want.

a	A
b	B
c	M
d	C
e	D
f	P
g	E

In this example, it would delete the cells M and P in column 2 so that both columns have the same values.

I have this code but it doesn't work quite right when I test it.

Sub Button2_Click()
For Each cell In Range("B2:B478")
If cell = cell.Offset(, -1) Then
Else
cell.delete
End If
Next cell
End Sub

 

[DanteAmor]

Try this:

Sub comparecolumns()
  Dim cell As Range, f As Range
  
  For Each cell In Range("A2", Range("A" & Rows.Count).End(3))
    If LCase(cell.Value) <> LCase(cell.Offset(, 1).Value) Then
      cell.Offset(, 1).ClearContents
      Set f = Range("B" & cell.Row, Range("B" & Rows.Count).End(3)).Find(cell.Value, , xlValues, xlWhole, , , False)
      If Not f Is Nothing Then cell.Offset(, 1).Value = f.Value
    End If
  Next
End Sub

 

[jolivanes]

Another one.

Sub Who_Knows()
Dim datArr, i As Long
datArr = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value
ReDim Preserve datArr(1 To UBound(datArr), 1 To 2)
    For i = LBound(datArr) To UBound(datArr)
        datArr(i, 2) = UCase(datArr(i, 1))
    Next i
Columns(2).ClearContents
Cells(1, 2).Resize(UBound(datArr, 1)).Value = Application.Index(datArr, , 2)
End Sub