Compare combination of 2 columns in 2 worksheets

[johnweber]

I am very new to Excel-VBA. The requirement is simple but I dont know any complex formulae or VBA,
Two columns (E and F), from two worksheets, need to be taken as a single composite column and compared with each other (the composite key is always unique).
This is the first sheet with the old data:

This is the second sheet with the new data:

For each matching combination of (Col E concat Col F) in both sheets, the difference (if any) of the amounts in column "I" needs to be calculated and this should be displayed in sheet 3 as below with the yellow highlights:

 

[mikerickson]

You might use SUMIFS

In you lower image, put =SUMIFS(Sheet1!$I:$I, Sheet1!$E:$E, E4, Sheet1!$F:F, F4) in I4
Change to sheet2 references for column J.

 

[JLGWhiz]

Here is some VBA

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, c As Range, lr1 As Long, lr2 As Long, fn As Range
Set sh1 = Sheets("Sheet1") 'Edit sheet name
Set sh2 = Sheets("Sheet2") 'Edit sheet name
Set sh3 = Sheets("Sheet3") 'Edit sheet name
lr1 = sh1.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
lr2 = sh2.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
sh1.Columns(1).Insert
sh2.Columns(1).Insert
    With sh1
        For i = 4 To lr1
            .Cells(i, 1) = .Cells(i, 6).Value & .Cells(i, 7).Value
        Next
    End With
    With sh2
         For i = 4 To lr2
            .Cells(i, 1) = .Cells(i, 6).Value & .Cells(i, 7).Value
        Next
    End With
    For Each c In sh1.Range("A4", sh1.Cells(Rows.Count, 1).End(xlUp))
        Set fn = sh2.Range("A:A").Find(c.Value, , xlValues)
            If Not fn Is Nothing Then
                If sh3.Range("C4") = "" Then
                    c.Offset(, 3).Resize(1, 7).Copy sh3.Range("C4")
                    sh2.Cells(fn.Row, 10).Resize(1, 2).Copy sh3.Cells(Rows.Count, 3).End(xlUp).Offset(, 7)
                    sh3.Cells(Rows.Count, 3).End(xlUp).Offset(, 9) = c.Offset(, 9).Value - fn.Offset(, 9).Value
                Else
                    c.Offset(, 3).Resize(1, 7).Copy sh3.Cells(Rows.Count, 3).End(xlUp)(2)
                    sh2.Cells(fn.Row, 10).Resize(1, 2).Copy sh3.Cells(Rows.Count, 3).End(xlUp).Offset(, 7)
                    sh3.Cells(Rows.Count, 3).End(xlUp).Offset(, 9) = c.Offset(, 9).Value - fn.Offset(, 9).Value
                End If
            End If
    Next
    sh1.Columns(1).Delete
    sh2.Columns(1).Delete
End Sub

 

[johnweber]

Used the formula you gave and Got It To Work! you the man! Thanks, Mike!!

 

[johnweber]

Oh my god JLG :O...*hands on head* its perfect
You just saved 20 people the work of comparing these!!
Just wonderful, god bless

 

[JLGWhiz]

Oh my god JLG :O...*hands on head* its perfect
You just saved 20 people the work of comparing these!!
Just wonderful, god bless

Happy to help, thanks for the feedback
Regards, JLg

 

[johnweber]

JLGWhiz, we got misses on the result sheet once we ran it on data
Ive attached the excel file which has the two data files as worksheets File1 and File2,

if you notice the result sheet, for the column combination of E and F from File1 and File2 i.e.
201801 /101
we dont have a row for the same in File3, the rows missing. We found that out once we applied an A-Z sorting on File3's column F.

upon taking a closer look at your code (which is still awesome by the way) it appears you have taken columns: F and G for concatenation and look up for the amounts:

Cells(i, 6).Value & .Cells(i, 7)

It should be E and F instead -

Cells(i, 6).Value & .Cells(i, 7)

Im guessing thats the reason, but when I changed just these values in the code, the code didnt work - maybe it has something more to do with the rest of the code.

Could you please look into it?

Again, the requirement is: based on a composite value (columns E + column F) in File1 and File2, compare the numbers in column J in both sheets and display highlighted differences if any in a separate sheet.

 

[johnweber]

I could not attach the excel but here are the screen grabs for a better view:

File 2:

File 3: