AUTOMATE RECONCILIATION

Sub GenerateSummaryReport()
    Dim wsSupplier As Worksheet
    Dim wsNS As Worksheet
    Dim wsSummary As Worksheet
    Dim lastRowSupplier As Long
    Dim lastRowNS As Long
    Dim totalSupplierAmount As Double
    Dim totalNSAmount As Double
    Dim i As Long, j As Long
    Dim summaryRow As Long
    Dim foundMatch As Boolean
    Dim supplierAmount As Double
    Dim nsAmount As Double
    Dim discrepancyTotal As Double

    ' Set the worksheets
    Set wsSupplier = ThisWorkbook.Sheets("Supplier SOA")
    Set wsNS = ThisWorkbook.Sheets("NS SOA")
    Set wsSummary = ThisWorkbook.Sheets("Summary")

    ' Clear the Summary sheet
    wsSummary.Cells.Clear

    ' Calculate total amounts
    totalSupplierAmount = Application.WorksheetFunction.Sum(wsSupplier.Range("C2:C" & wsSupplier.Cells(wsSupplier.Rows.Count, "C").End(xlUp).Row))
    totalNSAmount = Application.WorksheetFunction.Sum(wsNS.Range("C2:C" & wsNS.Cells(wsNS.Rows.Count, "C").End(xlUp).Row))

    ' Display totals and overall difference
    wsSummary.Cells(1, 1).Value = "Total Invoice Amount of Supplier SOA"
    wsSummary.Cells(1, 2).Value = totalSupplierAmount

    wsSummary.Cells(2, 1).Value = "Total Invoice Amount of NS SOA"
    wsSummary.Cells(2, 2).Value = totalNSAmount

    wsSummary.Cells(3, 1).Value = "Overall Difference"
    wsSummary.Cells(3, 2).Value = totalNSAmount - totalSupplierAmount

    ' Initialize discrepancy total
    discrepancyTotal = 0

    ' Set header for differences table
    summaryRow = 5
    wsSummary.Cells(summaryRow, 1).Value = "Date"
    wsSummary.Cells(summaryRow, 2).Value = "Invoice Number"
    wsSummary.Cells(summaryRow, 3).Value = "Supplier Amount"
    wsSummary.Cells(summaryRow, 4).Value = "NS Amount"
    wsSummary.Cells(summaryRow, 5).Value = "Difference in Amount"

    ' Find invoices in both SOAs with amount differences
    summaryRow = 6
    For i = 2 To wsSupplier.Cells(wsSupplier.Rows.Count, "A").End(xlUp).Row
        foundMatch = False
        For j = 2 To wsNS.Cells(wsNS.Rows.Count, "A").End(xlUp).Row
            If wsSupplier.Cells(i, 2).Value = wsNS.Cells(j, 2).Value Then
                supplierAmount = Val(wsSupplier.Cells(i, 3).Value)
                nsAmount = Val(wsNS.Cells(j, 3).Value)
                If supplierAmount <> nsAmount Then
                    wsSummary.Cells(summaryRow, 1).Value = wsSupplier.Cells(i, 1).Value
                    wsSummary.Cells(summaryRow, 2).Value = wsSupplier.Cells(i, 2).Value
                    wsSummary.Cells(summaryRow, 3).Value = supplierAmount
                    wsSummary.Cells(summaryRow, 4).Value = nsAmount
                    wsSummary.Cells(summaryRow, 5).Value = nsAmount - supplierAmount
                    discrepancyTotal = discrepancyTotal + (nsAmount - supplierAmount)
                    summaryRow = summaryRow + 1
                End If
                foundMatch = True
                Exit For
            End If
        Next j
    Next i

    ' Find invoices in Supplier SOA but not in NS SOA
    For i = 2 To wsSupplier.Cells(wsSupplier.Rows.Count, "A").End(xlUp).Row
        foundMatch = False
        For j = 2 To wsNS.Cells(wsNS.Rows.Count, "A").End(xlUp).Row
            If wsSupplier.Cells(i, 2).Value = wsNS.Cells(j, 2).Value Then
                foundMatch = True
                Exit For
            End If
        Next j

        If Not foundMatch Then
            wsSummary.Cells(summaryRow, 1).Value = wsSupplier.Cells(i, 1).Value
            wsSummary.Cells(summaryRow, 2).Value = wsSupplier.Cells(i, 2).Value
            wsSummary.Cells(summaryRow, 3).Value = Val(wsSupplier.Cells(i, 3).Value)
            wsSummary.Cells(summaryRow, 4).Value = "Not in NS SOA"
            wsSummary.Cells(summaryRow, 5).Value = Val(wsSupplier.Cells(i, 3).Value) ' Since NS Amount is missing
            discrepancyTotal = discrepancyTotal + Val(wsSupplier.Cells(i, 3).Value)
            summaryRow = summaryRow + 1
        End If
    Next i

    ' Find invoices in NS SOA but not in Supplier SOA
    For j = 2 To wsNS.Cells(wsNS.Rows.Count, "A").End(xlUp).Row
        foundMatch = False
        For i = 2 To wsSupplier.Cells(wsSupplier.Rows.Count, "A").End(xlUp).Row
            If wsNS.Cells(j, 2).Value = wsSupplier.Cells(i, 2).Value Then
                foundMatch = True
                Exit For
            End If
        Next i

        If Not foundMatch Then
            wsSummary.Cells(summaryRow, 1).Value = wsNS.Cells(j, 1).Value
            wsSummary.Cells(summaryRow, 2).Value = wsNS.Cells(j, 2).Value
            wsSummary.Cells(summaryRow, 3).Value = "Not in Supplier SOA"
            wsSummary.Cells(summaryRow, 4).Value = Val(wsNS.Cells(j, 3).Value)
            wsSummary.Cells(summaryRow, 5).Value = Val(wsNS.Cells(j, 3).Value) ' Since Supplier Amount is missing
            discrepancyTotal = discrepancyTotal + Val(wsNS.Cells(j, 3).Value)
            summaryRow = summaryRow + 1
        End If
        Next
       End Sub