Compare combination of 2 columns in 2 worksheets

johnweber

New Member
Joined
Feb 22, 2015
Messages
11
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:

 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,770
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,297
Office Version
2013
Platform
Windows
Here is some VBA

Code:
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

New Member
Joined
Feb 22, 2015
Messages
11
Used the formula you gave and Got It To Work! you the man! Thanks, Mike!!
:cool:
Code:
 

johnweber

New Member
Joined
Feb 22, 2015
Messages
11

ADVERTISEMENT

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

Well-known Member
Joined
Feb 7, 2012
Messages
12,297
Office Version
2013
Platform
Windows
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

New Member
Joined
Feb 22, 2015
Messages
11
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:
Code:
Cells(i, 6).Value & .Cells(i, 7)
It should be E and F instead -

Code:
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 :eek: - 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

New Member
Joined
Feb 22, 2015
Messages
11
I could not attach the excel but here are the screen grabs for a better view:



File 2:




File 3:

 
Last edited:

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,316
Messages
5,510,554
Members
408,798
Latest member
jitu20feb

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top