Bank Vs Ledger Matching Tool

📊 Excel VBA Tool: Bank vs GL Reconciliation

This automation tool compares bank transactions with your general ledger and identifies mismatches, helping you stay audit-ready and confident in your records.

✨ Key Features

• Load & normalize transactions from separate sheets
• Match entries by date and amount
• Highlight unmatched or duplicate transactions
• Generate a summary and save reports with timestamps
• Visual dashboard with reconciliation status

📁 VBA Code – Module 1: Load & Normalize

Sub LoadAndNormalizeData() Dim wsBank As Worksheet, wsGL As Worksheet Set wsBank = Sheets("Bank") Set wsGL = Sheets("GL") wsBank.Range("A2:A1000").NumberFormat = "dd-mmm-yyyy" wsGL.Range("A2:A1000").NumberFormat = "dd-mmm-yyyy" wsBank.Range("C2:C1000").Value = Application.WorksheetFunction.Abs(wsBank.Range("C2:C1000").Value) wsGL.Range("C2:C1000").Value = Application.WorksheetFunction.Abs(wsGL.Range("C2:C1000").Value) End Sub

🔗 VBA Code – Module 2: Match Transactions

Sub MatchTransactions() Dim bankWS As Worksheet, glWS As Worksheet Set bankWS = Sheets("Bank") Set glWS = Sheets("GL") For bankRow = 2 To 1000 bankDate = bankWS.Cells(bankRow, 1).Value bankAmt = bankWS.Cells(bankRow, 3).Value For glRow = 2 To 1000 If glWS.Cells(glRow, 4).Value = "" Then If glWS.Cells(glRow, 1).Value = bankDate And _ Abs(glWS.Cells(glRow, 3).Value - bankAmt) < 0.01 Then bankWS.Cells(bankRow, 5).Value = "MATCHED" glWS.Cells(glRow, 4).Value = "MATCHED" Exit For End If End If Next glRow Next bankRow End Sub

📦 VBA Code – Module 3: Summary & Export

Sub SummaryAndExport() Dim matchedCount As Long, unmatchedCount As Long matchedCount = WorksheetFunction.CountIf(Sheets("Bank").Range("E2:E1000"), "MATCHED") unmatchedCount = WorksheetFunction.CountIf(Sheets("Bank").Range("E2:E1000"), "UNMATCHED") MsgBox "Matched: " & matchedCount & vbCrLf & "Unmatched: " & unmatchedCount ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\Recon_" & Format(Now, "yyyymmdd_hhmmss") & ".xlsx" End Sub

📈 Dashboard Setup (Excel Formulas)

Create a sheet called "Dashboard" and insert formulas:

• =COUNTIF(Bank!E2:E1000,"MATCHED")
• =COUNTIF(Bank!E2:E1000,"UNMATCHED")
• =COUNTA(Bank!E2:E1000)

Add a pie chart to visualize matched vs unmatched records.

📞 Need a Custom Email Automation Tool?

If you'd like to customize this template for your business—whether it's branding, attachments, or advanced logic—feel free to connect with us. We’d love to help you streamline your workflow and save time.

👉 Contact us today to get started with your personalized Excel email automation solution!

💬 Comment below if you'd like the source code or help customizing it.

---

🛠️ Built with Excel VBA | © chiragcoder.com