Compare the columns in two files and results in a separate column

[sindhuja]

Hi..

I have two excel files (file A and file B) and i need to compare the columns B and D of file A with the column H and L of file B respectively.

One is ID and the other column is amount column and i need the results of all the entries matches in a separate sheet and the non matching entries in other sheet.

Ex - In File A

ID Amount
A3397 100.32
A3398 700.59
A3387 10.03


File B
ID Amount
A3398 700.56
A3387 15.02
A3399 800.82

In the above example : ID A3387 in file A does not matches with File B so this is to be displayed in sheet named Unmatched. A3397 and A3399 should also be in unmatched sheet.

ID A3398 in file A - the same id is in file B but with difference in amount. Since the difference is < 0.10 we can consider that as a match and put in sheet named Matched. If amount difference between file A and B is + or - 0.10 we can consider them as matched.

Can this be done using macros since i have very large data to be compared in the same way.

Kindly assist..

-sindhuja

[mdmackillop]

Untested
Option Explicit

Sub Test()
Dim wsA As Worksheet, wsB As Worksheets
Dim r As Range, cel As Range, c As Range, tgt As Range

Set wsA = Workbooks("A.xls").Sheets(1)
Set wsB = Workbooks("B.xls").Sheets(1)
Set r = wsB.Columns(8)

For Each cel In wsA.Columns(2).SpecialCells(xlCellTypeConstants)
Set c = r.Find(cel, lookat:=xlWhole)
Set tgt = Sheets("unmatched").Cells(Rows.Count, 1).End(xlUp)(2)
If c Is Nothing Then
tgt = cel
tgt.Offset(, 1) = cel.Offset(, 2)
Else
If Abs(c.Offset(, 2) - cel.Offset(, 2)) > 0.1 Then
tgt = cel
tgt.Offset(, 1) = cel.Offset(, 2)
End If
End If
Next
End Sub

[sindhuja]

Hi md..:friends:

Thank you for the coding.

In the unmatched sheet of File A it gives only the results of unmatched items of File A whereas i need the items from File B.

Please find the attachment with the sample data and the expected highlighted output in the "unmatched" sheet.

Coding works only when there is n header data in the files.

-Sindhuja