Slight modification needed - probably take most 3 minutes to figure out

[Shaolin]

The code works except it compares column A values with column B values and prints out the values in column A and not column B

Sub compareColumns()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim NextRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To LastRow

If IsError(Application.Match(.Cells(i, TEST_COLUMN).Value, .Columns(2), 0)) Then

NextRow = NextRow + 1
.Cells(i, TEST_COLUMN).Copy .Cells(NextRow, "C")
End If
Next i

End With

End Sub


How do I change this such that the code prints values in both column A and B in column C? So, column C has the values that are in both A and B.

thx

[Bob Phillips]

Sub compareColumns()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim NextRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To LastRow

If IsError(Application.Match(.Cells(i, TEST_COLUMN).Value, .Columns(2), 0)) Then

NextRow = NextRow + 1
.Cells(NextRow, "C") = .Cells(i, TEST_COLUMN).Value & _
.Cells(i, TEST_COLUMN).Offset(0, 1).Value
End If
Next i

End With

End Sub

[Shaolin]

Thanks so much. Need some more help though.

The result is that it adds the value of column A and column B together in column C.

For example column A has value "hello" and B has "bye", then "hellobye" is printed in C.

If cell A2 has value "hello" and B38 also has "hello", then "hello" should be printed in column C.

Much appreciated!!

[Frosty]

Then just add that test?



Sub compareColumns()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim NextRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To LastRow

If IsError(Application.Match(.Cells(i, TEST_COLUMN).Value, .Columns(2), 0)) Then

NextRow = NextRow + 1
If .Cells(i TEST_COLUMN).Value = .Cells(i, TEST_COLUMN).Offset(0,1).Value then
.Cells(NextRow, "C") = .Cells(i, TEST_COLUMN).Value
Else
.Cells(NextRow, "C") = .Cells(i, TEST_COLUMN).Value & _
.Cells(i, TEST_COLUMN).Offset(0, 1).Value
End If
End If
Next i

End With

End Sub

[Shaolin]

Thanks, I'm lost!!

[Bob Phillips]

Does lost mean sorted or not?