COMPARE 2 XL WORKBOOKS & PASTE DATA

[trivrain]

Hello,

I am trying to achieve the below:

With 2 excel spreads T1 and T2
I want to compare the FULL content of column B (1st name), C (last name), & E (company) in T1 to the content of A (1st name), B (last name), D (company) in T2 for each records.
If for a record all above cells match, copy the content of T1's column J in T2's column M.
If not all match highlight the record in red in T1 .

Can anyone help ?

Could be based on the below previous code from XLD

[VBA]Public Sub ProcessData()
Dim targetWb As Workbook
Dim targetWs As Worksheet
Dim Lastrow As Long
Dim Matchrow As Long
Dim i As Long

Application.ScreenUpdating = False

Set targetWb = Workbooks("T2.xls")
Set targetWs = targetWb.Worksheets(1)

With Workbooks("T1.xls").Worksheets(1)

Lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 2 To Lastrow

Matchrow = 0
On Error Resume Next
Matchrow = Application.Match(.Cells(i, "B").Value2, targetWs.Columns("A"), 0)
On Error GoTo 0
If Matchrow > 0 Then

.Cells(i, "R").Value2 = targetWs.Cells(Matchrow, "Q").Value2
End If
Next i
End With

Set targetWs = Nothing
Set targetWb = Nothing

Application.ScreenUpdating = True
End Sub[/VBA]

[mdmackillop]

Create a helper column in both sheets =B&C&E and =A&B&D and compare these two columns. Make your changes based on that simpler comparison.

[trivrain]

Hi,
Thank you. Yes indeed, but I was hoping for a more straight forward, concatainer-formula-free way to achieve this. Any idea how to insert the B&C&E check into the VBA code ? Should I just try a [VBA]Cells(i, "B&C&E").Value2,[/VBA] or is that pure nonsense ?