Compare 2 columns in one worksheet to another

[Kinez101]

I have a file with over 100K rows and was wondering if there is a quick way to compare columns C and D in Previous Month to Current Month and vice-versa then output differences to Differences worksheet.

 

[alansidman]

PQ is part of Excel in versions 2010 and later. In 2016, it is part of Excel and does not require a download. It is called Get and Transform. Otherwise, download it. It may take several days for me to build a VBA solution as I am in the midst of relocating from my summer house to winter house and time is not abundant. Can you upload to a third party some sample worksheets for each month as I don't wish to try and reconstruct your data. This is necessary in order to test.

 

[AhoyNC]

Maybe something like this:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	List 1				List 2			List 1 not in List 2
2	BIC CODE	BRANCH CODE			BIC CODE	BRANCH CODE		BIC CODE	BRANCH CODE
3	AAAARSBG	XXX			AAAARSBG	XXX		AAAMFRP1	XXX
4	AAACKWKW	XXX			AAACKWKW	XXX		AABAFI22	TMS
5	AAADFRP1	XXX			AAADFRP1	XXX
6	AAAGFRP1	XXX			AAAGFRP1	XXX
7	AAAJBG21	XXX			AAAJBG21	XXX
8	AAALSARI	ALK			AAALSARI	ALK
9	AAALSARI	CTD			AAALSARI	CTD
10	AAALSARI	JED			AAALSARI	JED
11	AAALSARI	RYD			AAALSARI	RYD		List 2 not in List 1
12	AAALSARI	XXX			AAALSARI	XXX		AAAMFRP1	TTT
13	AAAMFRP1	XXX			AAAMFRP1	TTT		AABAFI23	TMS
14	AAAOFRP1	XXX			AAAOFRP1	XXX
15	AAAPBGS1	XXX			AAAPBGS1	XXX
16	AAASTHB1	XXX			AAASTHB1	XXX
17	AABAFI22	TMS			AABAFI23	TMS
18	AABAFI22	XXX			AABAFI22	XXX
19	AABASESS	TMS			AABASESS	TMS
20	AABASESS	XXX			AABASESS	XXX
21	AABMIT22	XXX			AABMIT22	XXX
Sheet

 

[Kinez101]

This code is great start, just need it modified to read Column C and D between 2 sheets and not loop through 100K rows of data:

Option Explicit
Sub CompareYesterdayTodayV2()
' stanleydgromjr, 08/09/2011
' http://www.excelforum.com/excel-programming/785472-compare-two-worksheets-and-present-differences-in-a-third.html
Dim wY As Worksheet, wT As Worksheet, wC As Worksheet
Dim c As Range, FR As Long, NR As Long, cc As Long, dd As Long, NC As Long, LC As Long
Application.ScreenUpdating = False
Set wY = Worksheets("Previous Month")
Set wT = Worksheets("Current Month")
Set wC = Worksheets("Differences")
wC.UsedRange.Clear
LC = wT.Cells(1, Columns.Count).End(xlToLeft).Column
wY.Range(wY.Cells(1, 1), wY.Cells(1, LC)).Copy wC.Range("A1")
For Each c In wY.Range("C2", wY.Range("C" & Rows.Count).End(xlUp))
  FR = 0
  On Error Resume Next
  FR = Application.Match(c, wT.Columns(2), 0)
  On Error GoTo 0
  If FR = 0 Then
    NR = wC.Range("C" & wC.Rows.Count).End(xlUp).Offset(1).Row
    wY.Range(wY.Cells(c.Row, 1), wY.Cells(c.Row, LC)).Copy wC.Range("A" & NR)
    wC.Range("A" & NR).Resize(, LC).Interior.Color = 255
  End If
Next c
For Each c In wT.Range("C2", wT.Range("C" & Rows.Count).End(xlUp))
  If c <> "" Then
    FR = 0
    On Error Resume Next
    FR = Application.Match(c, wY.Columns(2), 0)
    On Error GoTo 0
    If FR = 0 Then
      NR = wC.Range("C" & wC.Rows.Count).End(xlUp).Offset(1).Row
      wT.Range(wT.Cells(c.Row, 1), wT.Cells(c.Row, LC)).Copy wC.Range("A" & NR)
      wC.Range("A" & NR).Resize(, LC).Interior.Color = 65280
    End If
  End If
Next c
For Each c In wY.Range("C2", wY.Range("C" & Rows.Count).End(xlUp))
  If c <> "" Then
    FR = 0
    On Error Resume Next
    FR = Application.Match(c, wT.Columns(2), 0)
    On Error GoTo 0
    If FR <> 0 Then
      NR = wC.Range("B" & wC.Rows.Count).End(xlUp).Offset(1).Row
      NC = 0
      For cc = 3 To LC Step 1
        If wY.Cells(c.Row, cc) <> wT.Cells(FR, cc) Then NC = NC + 1
      Next cc
      If NC = LC Then
        wC.Cells(NR, 1).Resize(, 2).Value = wY.Cells(c.Row, 1).Resize(, 2).Value
        For dd = 3 To LC Step 1
          With wC.Cells(NR, dd)
            .NumberFormat = "@"
            .Value = wY.Cells(c.Row, dd).Value & "/" & wT.Cells(FR, dd).Value
          End With
        Next dd
        wC.Range("C" & NR).Resize(, LC).Interior.Color = 65535
      ElseIf NC > 0 And NC < LC Then
        wC.Cells(NR, 1).Resize(, 2).Value = wY.Cells(c.Row, 1).Resize(, 2).Value
        For cc = 3 To LC Step 1
          If wY.Cells(c.Row, cc) <> wT.Cells(FR, cc) Then
            With wC.Cells(NR, cc)
              .NumberFormat = "@"
              .Value = wY.Cells(c.Row, cc).Value & "/" & wT.Cells(FR, cc).Value
              .Interior.Color = 65535
            End With
          End If
        Next cc
      End If
    End If
  End If
Next c
wC.UsedRange.Columns.AutoFit
wC.Activate
Application.ScreenUpdating = True
End Sub