Reconciliation of two sets of data

Hello,

I am hoping you can help.

I have two sets of data that I pull into a spreadsheet.
Data1 and Data2.

I am looking to create a macro, which recs the two sets of data and spits out only errors.

I have attached an example spreadsheet and coloured in examples of various errors I might come across.

The data should be reconciled using a concatenation of ACCOUNT and REFERENCE.

Example 1 (highlighted in green) - Data1 has Value 1 as -226, however, Data2 has Value 1 as -225

I would highlight this as an error on the Rec sheet as Incorrect Value 1 and display the Account, Reference and the Data2 Value 1

The procedure would then run through again and notice Data1 has a Value 2 as -229, however, Data2 has a Value 2 as -228.

I would highlight this as an error on the Rec sheet as Incorrect Value 2 and display the Account, Reference and the Data2 Value 2

Example 2 (highlighted in orange) - Data1 has a CCY of GBP, however, Data2 has a CCY of EUR

I would highlight this as an error on the Rec sheet as Incorrect CCY and display the Account, Reference and the Data2 CCY value

The same would apply to date etc.

If the Account & Reference concatenation cannot be found on the Data2 sheet an error should be displayed advising the Account and Reference, which is missing. (highlighted in yellow)


Is anyone able to help?

Thanks

Hello mccrimmon,

The following macro has been added to the attached workbook. The original reconciliation sheet "Rec" has been named "Rec1". I did this to compare the macro results against your original data. A button has been added on the "Rec" worksheet to run the macro.

'Written: June 25, 2010
'Author:  Leith Ross
'Thread: http://www.excelforum.com/excel-programming/734362-reconciliation-of-two-sets-of-data.html
'Poster: mccrimmon
'Posted: June 24, 2010

Sub Reconcile()

  Dim Account As String
  Dim Cell As Range
  Dim Details As Variant
  Dim R As Long
  Dim RecWks As Worksheet
  Dim RefNum As String
  Dim Rng As Range
  Dim RngEnd As Range
  Dim TAJ As Object          'Transaction Journal (master)
  Dim Wks As Worksheet
  
    Set RecWks = Worksheets("Rec")
    Set Wks = Worksheets("Data1")
    
    Set Rng = Wks.Range("A2:G2")
    Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
    Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Wks.Range(Rng, RngEnd))
    
    Set TAJ = CreateObject("Scripting.Dictionary")
    TAJ.CompareMode = vbTextCompare
    
      For Each Cell In Rng.Columns(1).Cells
        Account = Trim(Cell)
        RefNum = Trim(Cell.Offset(0, 2))
        If Account <> "" Then
          If Not TAJ.Exists(Account & RefNum) Then
            ReDim Details(3)
              Details(0) = Cell.Offset(0, 3).Value   'Date
              Details(1) = Cell.Offset(0, 4).Value   'Currency
              Details(2) = Cell.Offset(0, 5).Value   'Value1
              Details(3) = Cell.Offset(0, 6).Value   'Value2
            TAJ.Add Account & RefNum, Details
          End If
        End If
      Next Cell
      
       'Clear any previous reconciliation errors
        With RecWks
          R = 3   'Starting row for reconciliation errors
          .Range(.Rows(R + 1), .Rows(Rows.Count)).Clear
        End With
        
        Set Wks = Worksheets("Data2")
      
        Set Rng = Wks.Range("A2:I2")
        Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
        Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Wks.Range(Rng, RngEnd))
      
          For Each Cell In Rng.Columns(3).Cells
            Account = Trim(Cell)
            RefNum = Trim(Cell.Offset(0, 2))
            If Not TAJ.Exists(Account & RefNum) Then
              'Missing
                RecWks.Cells(R, "C").Value = "Missing"
                RecWks.Cells(R, "D") = Account
                RecWks.Cells(R, "E") = RefNum
                R = R + 1
            Else
              'Check Details for errors
               Details = TAJ(Account & RefNum)
                 If Cell.Offset(0, 4).Value <> Details(0) Then
                   RecWks.Cells(R, "C").Value = "Incorrect Date"
                   RecWks.Cells(R, "D") = Account
                   RecWks.Cells(R, "E") = RefNum
                   RecWks.Cells(R, "G") = Cell.Offset(0, 4).Value
                   R = R + 1
                 End If
                 If Cell.Offset(0, 3).Value <> Details(1) Then
                   RecWks.Cells(R, "C").Value = "Incorrect Currency"
                   RecWks.Cells(R, "D") = Account
                   RecWks.Cells(R, "E") = RefNum
                   RecWks.Cells(R, "F") = Cell.Offset(0, 3).Value
                   R = R + 1
                 End If
                 If Cell.Offset(0, 5).Value <> Details(2) Then
                   RecWks.Cells(R, "C").Value = "Incorrect Value 1"
                   RecWks.Cells(R, "D") = Account
                   RecWks.Cells(R, "E") = RefNum
                   RecWks.Cells(R, "H") = Cell.Offset(0, 5).Value
                   R = R + 1
                 End If
                 If Cell.Offset(0, 6).Value <> Details(3) Then
                   RecWks.Cells(R, "C").Value = "Incorrect Value 2"
                   RecWks.Cells(R, "D") = Account
                   RecWks.Cells(R, "E") = RefNum
                   RecWks.Cells(R, "H") = Cell.Offset(0, 6).Value
                   R = R + 1
                 End If
            End If
          Next Cell
              
End Sub

Excellent work Leith.

Thanks very much.
Makes a lot more sense to see it written out like that.

I've seen examples before and struggled to get my head round it.

Hi Leith,

Wondering if you can assist me further.
I am looking to complete a number of recs and have all the results on the Rec sheet.

How do I get it so that when I complete the first reconciliation, I can go and complete a further reconciliation between Data3 and Data2 and have the results added to the end of the previous Rec like the attached example?

Thanks again
Much appreciated

Hello mccrimmon,

Are you saying that after Data2 is reconciled against Data1, you what to reconcile Data3 against Data1 and record the errors on the Rec sheet?

Originally Posted by Leith Ross

Hello mccrimmon,

Are you saying that after Data2 is reconciled against Data1, you what to reconcile Data3 against Data1 and record the errors on the Rec sheet?

Sorry, the sheet names probably confuse matters.

After reconciling Data1 against Data2 and recording the errors on the Rec sheet, I want to rec Data3 against Data2 and record the error at the end of the previous errors on the Rec Sheet.

Thanks

Hello mccrimmon,

If an error is reported while reconciling Data1 and Data2 that makes sense because we know Data1 is the master file. If there are now 2 master files then how do you know where the error is?

In your last post the reference BM3-REC3example doesn't exists on Data1 and would be reported missing. Reconciling Data2 and Data3 reports a currency error. Since we know Data2 is the sheet being reconciled, how due 2 errors from 2 different "masters" help the reconciliation process, since there is no record of which sheet the error occurred on?

Originally Posted by snb

or:

Sub tst()
  On Error GoTo XL90
  sm = Sheets("Master").Cells(1, 1).CurrentRegion
  
  For Each Sh In Sheets
     If Left(Sh.Name, 7) = "Account" Then
        sn = Sh.Cells(1, 1).CurrentRegion
        For j = 2 To UBound(sn)
           With Sheets("Master").Columns(5).Find(sn(j, 3))
              For jj = 1 To 7
                  If jj <> 2 And sn(j, jj) <> sm(.Row, jj + 2 + IIf(jj = 4, 1, IIf(jj = 5, -1, 0))) Then Sh.Cells(j, jj).Interior.ColorIndex = 4
              Next
           End With
XL91:
        Next
    End If
Next
Exit Sub

XL90:
   Sheets("Account1").Cells(j, 1).Resize(, 7).Interior.ColorIndex = 4
   GoTo XL91

End Sub

Thanks for sharing your solution.

The code is extremely fast, however, I really need all the errors written out for the user as there is potentially thousands of accounts that need reconciled so do not want the user having to scroll through each sheet to ensure there are no errors.

Sub tst()
  On Error GoTo XL90
  sm = Sheets("Master").Cells(1, 1).CurrentRegion
  
  For Each Sh In Sheets
     If Left(Sh.Name, 7) = "Account" Then
        sn = Sh.Cells(1, 1).CurrentRegion
        For j = 2 To UBound(sn)
           With Sheets("Master").Columns(5).Find(sn(j, 3))
               If sm(.Row, 3) &  sm(.Row, 5) & sm(.Row, 6) & sm(.Row, 7) & sm(.Row, 8) & sm(.Row, 9) <> sn(j, 1) & sn(j, 3) & sn(j, 5) & sn(j, 4) & sn(j, 6) & sn(j, 7) Then
              For jj = 1 To 7
                  If jj <> 2 then c01=c01 & "|" & iif(sn(j, jj) <> sm(.Row, jj + 2 + IIf(jj = 4, 1, IIf(jj = 5, -1, 0))) or jj=3,sn(j,jj),"")
              Next
              c01=c01& vbcr
              End If
           End With
XL91:
        Next
    End If
Next

With Sheets.add
   .cells(1,1).resize(ubound(split(c01,vbr))+1)=worksheefunction.transpose(split(c01,vbcr))
   .columns(1).Texttocolumns , 1, -4142, , False, False, False, False, True, "|"
end with 
Exit Sub

XL90:
  c01=c01 & sn(j,1) & "|" & sn(j,3) & "|" & sn(j,4) & "|" & sn(j,6) &"|" & sn(j,7) & "|" & sn(j,7) & vbcr
GoTo XL91
End Sub

Originally Posted by snb

Sub tst()
  On Error GoTo XL90
  sm = Sheets("Master").Cells(1, 1).CurrentRegion
  
  For Each Sh In Sheets
     If Left(Sh.Name, 7) = "Account" Then
        sn = Sh.Cells(1, 1).CurrentRegion
        For j = 2 To UBound(sn)
           With Sheets("Master").Columns(5).Find(sn(j, 3))
              if sm(.row,jj+2)&sm(.row,jj+4) & sm(.row,jj+5)& sm(.row,jj+6) & sm(.row,jj+7) & sm(.row,jj+8) & sm(.row.j+9)<>sn(j,1) & sn(j,3) & sn(j,4)& sn(j,6)&sn(j,7)&sn(j,7) then 
              For jj = 1 To 7
                  If jj <> 2 then c01 & "|" & iif(sn(j, jj) <> sm(.Row, jj + 2 + IIf(jj = 4, 1, IIf(jj = 5, -1, 0))) or jj=3,sn(j),"")
              Next
              c01=c01& vbcr
              End If
           End With
XL91:
        Next
    End If
Next

With Sheets.add
   .cells(1,1).resize(ubound(split(c01,vbr))+1)=worksheefunction.transpose(split(c01,vbcr))
   .columns(1).Texttocolumns , 1, -4142, , False, False, False, False, True, "|"
end with 
Exit Sub

XL90:
  c01=c01 & sn(j,1) & "|" & sn(j,3) & "|" & sn(j,4) & "|" & sn(j,6) &"|" & sn(j,7) & "|" & sn(j,7) & vbcr
GoTo XL91
End Sub

Looks like theres an error in the code.
Receving a syntax error on this line of code.

If jj <> 2 Then c01 & "|" & IIf(sn(j, jj) <> sm(.Row, jj + 2 + IIf(jj = 4, 1, IIf(jj = 5, -1, 0))) Or jj = 3, sn(j), "")

Is it possible to explain what each part of the code is doing?

I am trying to learn VBA so the more I can learn from this forum the better.
Leiths original was very useful and has helped me understand what its doing.

If jj <> 2 Then c01= c01 & "|" & IIf(sn(j, jj) <> sm(.Row, jj + 2 + IIf(jj = 4, 1, IIf(jj = 5, -1, 0))) Or jj = 3, sn(j,jj), "")

Originally Posted by snb

If jj <> 2 Then c01= c01 & "|" & IIf(sn(j, jj) <> sm(.Row, jj + 2 + IIf(jj = 4, 1, IIf(jj = 5, -1, 0))) Or jj = 3, sn(j,jj), "")

Now receiving an Object error on the below:

              If sm(.Row, jj + 2) & sm(.Row, jj + 4) & sm(.Row, jj + 5) & sm(.Row, jj + 6) & sm(.Row, jj + 7) & sm(.Row, jj + 8) & sm(.Row.j + 9) <> sn(j, 1) & sn(j, 3) & sn(j, 4) & sn(j, 6) & sn(j, 7) & sn(j, 7) Then

Originally Posted by snb

If sm(.Row, 3) &  sm(.Row, 5) & sm(.Row, 6) & sm(.Row, 7) & sm(.Row, 8) & sm(.Row, 9) <> sn(j, 1) & sn(j, 3) & sn(j, 5) & sn(j, 4) & sn(j, 6) & sn(j, 7) Then

424 object required error on this now

   .Cells(1, 1).Resize(UBound(Split(c01, vbr)) + 1) = worksheefunction.Transpose(Split(c01, vbCr))

Any suggestions?

Originally Posted by snb

Buy some spectacles....
.Cells(1, 1).Resize(UBound(Split(c01, vbr)) + 1) = worksheetfunction.Transpose(Split(c01, vbCr))

Apologies, my bad.

However, I have run your code but all it does is create a new sheet and populates the first reference and values.

I dont think its what I am after.

Thanks for your help, its really appreciated, however, im really looking to learn from this aswell as manage the solution and I am not understanding any of what you have provided.

Thanks again though.

Hello Brian,

The macro now works will all the account sheets. Each account sheet is checked using the Transaction Journal entries from the "Master" worksheet. All errors found in an account and reference line item will be logged to the "Rec" sheet. Each entry is now accompanied by the name of the worksheet. Here is the amended macro and workbook containing it.

'Updated: June 28, 2010 - Corrected previous errors
'Author:  Leith Ross
'Thread: http://www.excelforum.com/excel-programming/734362-reconciliation-of-two-sets-of-data.html
'Poster: mccrimmon
'Posted: June 24, 2010

Sub Reconcile()

  Dim Account As String
  Dim Cell As Range
  Dim Details As Variant
  Dim R As Long
  Dim RecWks As Worksheet
  Dim RefNum As String
  Dim Rng As Range
  Dim RngEnd As Range
  Dim TAJ As Object          'Transaction Journal (master)
  Dim Wks As Worksheet
  
    Set RecWks = Worksheets("Rec")
    Set Wks = Worksheets("Master")
    
    Set Rng = Wks.Range("A2:I2")
    Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
    Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Wks.Range(Rng, RngEnd))
    
    Set TAJ = CreateObject("Scripting.Dictionary")
    TAJ.CompareMode = vbTextCompare
    
      For Each Cell In Rng.Columns(3).Cells
        Account = Trim(Cell)
        RefNum = Trim(Cell.Offset(0, 2))
        If Account <> "" Then
          If Not TAJ.Exists(Account & RefNum) Then
            ReDim Details(3)
              Details(0) = Cell.Offset(0, 3).Value   'Currency
              Details(1) = Cell.Offset(0, 4).Value   'Date
              Details(2) = Cell.Offset(0, 5).Value   'Value1
              Details(3) = Cell.Offset(0, 6).Value   'Value2
            TAJ.Add Account & RefNum, Details
          End If
        End If
      Next Cell
      
       'Clear any previous reconciliation errors
        With RecWks
          R = 2   'Header row for reconciliation errors
          .Range(.Rows(R + 1), .Rows(Rows.Count)).Clear
        End With
        
       'Check each account against the master data in the TAJ
        For Each Wks In Worksheets
          If Wks.Name <> "Rec" And Wks.Name <> "Rec1" And Wks.Name <> "Master" Then
            Set Rng = Wks.Range("A2:G2")
            Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
            Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Wks.Range(Rng, RngEnd))
              For Each Cell In Rng.Columns(1).Cells
                Account = Trim(Cell)
                RefNum = Trim(Cell.Offset(0, 2))
                Addx = Cell.Address
                If Not TAJ.Exists(Account & RefNum) Then
                  'Is entry Missing
                    R = R + 1
                    RecWks.Cells(R, "B") = Wks.Name
                    RecWks.Cells(R, "C").Value = "Missing"
                    RecWks.Cells(R, "D") = Account
                    RecWks.Cells(R, "E") = RefNum
                Else
                  'Check Details for errors
                   Details = TAJ(Account & RefNum)
                     If Cell.Offset(0, 3).Value <> Details(1) Then
                       R = R + 1
                       RecWks.Cells(R, "B") = Wks.Name
                       RecWks.Cells(R, "C").Value = "Incorrect Date"
                       RecWks.Cells(R, "D") = Account
                       RecWks.Cells(R, "E") = RefNum
                       RecWks.Cells(R, "G") = Cell.Offset(0, 3).Value
                     End If
                     If Cell.Offset(0, 4).Value <> Details(0) Then
                       R = R + 1
                       RecWks.Cells(R, "B") = Wks.Name
                       RecWks.Cells(R, "C").Value = "Incorrect Currency"
                       RecWks.Cells(R, "D") = Account
                       RecWks.Cells(R, "E") = RefNum
                       RecWks.Cells(R, "F") = Cell.Offset(0, 4).Value
                     End If
                     If Cell.Offset(0, 5).Value <> Details(2) Then
                       R = R + 1
                       RecWks.Cells(R, "B") = Wks.Name
                       RecWks.Cells(R, "C").Value = "Incorrect Value 1"
                       RecWks.Cells(R, "D") = Account
                       RecWks.Cells(R, "E") = RefNum
                       RecWks.Cells(R, "H") = Cell.Offset(0, 5).Value
                     End If
                     If Cell.Offset(0, 6).Value <> Details(3) Then
                       R = R + 1
                       RecWks.Cells(R, "B") = Wks.Name
                       RecWks.Cells(R, "C").Value = "Incorrect Value 2"
                       RecWks.Cells(R, "D") = Account
                       RecWks.Cells(R, "E") = RefNum
                       RecWks.Cells(R, "H") = Cell.Offset(0, 6).Value
                     End If
                End If
              Next Cell
          End If
        Next Wks
        
End Sub