Attribute VB_Name = "BankReconciliationModule"
Option Explicit

' MacroWise Course Deliverable
' Bank Reconciliation VBA automation module
' Compatible with Excel 2016+
'
' Expected worksheet setup:
'   1. Bank Statement
'   2. General Ledger
'
' The macro detects common header names, matches transactions by amount, date,
' reference, and description, flags unmatched rows, and builds a reconciliation report.

Private Const BANK_SHEET_NAME As String = "Bank Statement"
Private Const GL_SHEET_NAME As String = "General Ledger"
Private Const REPORT_SHEET_NAME As String = "Reconciliation Report"
Private Const STATUS_HEADER As String = "Reconciliation Status"
Private Const MATCHED_TO_HEADER As String = "Matched To"
Private Const MATCH_SCORE_HEADER As String = "Match Score"
Private Const MATCHED_ON_HEADER As String = "Matched On"

Public Sub RunBankReconciliation()
    Dim bankSheet As Worksheet
    Dim glSheet As Worksheet
    Dim reportSheet As Worksheet
    Dim bankHeaderRow As Long
    Dim glHeaderRow As Long
    Dim bankLastRow As Long
    Dim glLastRow As Long
    Dim bankDateCol As Long
    Dim bankAmountCol As Long
    Dim bankDescCol As Long
    Dim bankRefCol As Long
    Dim glDateCol As Long
    Dim glAmountCol As Long
    Dim glDescCol As Long
    Dim glRefCol As Long
    Dim bankStatusCol As Long
    Dim bankMatchedToCol As Long
    Dim bankScoreCol As Long
    Dim bankMatchedOnCol As Long
    Dim glStatusCol As Long
    Dim glMatchedToCol As Long
    Dim glScoreCol As Long
    Dim glMatchedOnCol As Long
    Dim glBuckets As Object
    Dim matchedGlRows As Object
    Dim bankRow As Long
    Dim bestGlRow As Long
    Dim bestScore As Long
    Dim matchedCount As Long

    On Error GoTo CleanFail

    Set bankSheet = RequireWorksheet(ThisWorkbook, BANK_SHEET_NAME)
    Set glSheet = RequireWorksheet(ThisWorkbook, GL_SHEET_NAME)

    bankHeaderRow = DetectHeaderRow(bankSheet, 10)
    glHeaderRow = DetectHeaderRow(glSheet, 10)
    bankLastRow = LastUsedRow(bankSheet)
    glLastRow = LastUsedRow(glSheet)

    bankDateCol = FindColumn(bankSheet, bankHeaderRow, Array("date", "posted date", "posting date", "transaction date"))
    bankAmountCol = FindColumn(bankSheet, bankHeaderRow, Array("amount", "value", "net amount"))
    bankDescCol = FindColumn(bankSheet, bankHeaderRow, Array("description", "details", "memo", "narration"))
    bankRefCol = FindColumn(bankSheet, bankHeaderRow, Array("reference", "check number", "document", "id"))

    glDateCol = FindColumn(glSheet, glHeaderRow, Array("date", "posting date", "effective date", "journal date"))
    glAmountCol = FindColumn(glSheet, glHeaderRow, Array("amount", "net amount", "transaction amount"))
    glDescCol = FindColumn(glSheet, glHeaderRow, Array("description", "memo", "details", "journal entry"))
    glRefCol = FindColumn(glSheet, glHeaderRow, Array("reference", "document", "journal number", "id"))

    ValidateCoreColumns bankDateCol, bankAmountCol, "Bank Statement"
    ValidateCoreColumns glDateCol, glAmountCol, "General Ledger"

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    bankStatusCol = EnsureOutputColumn(bankSheet, bankHeaderRow, STATUS_HEADER)
    bankMatchedToCol = EnsureOutputColumn(bankSheet, bankHeaderRow, MATCHED_TO_HEADER)
    bankScoreCol = EnsureOutputColumn(bankSheet, bankHeaderRow, MATCH_SCORE_HEADER)
    bankMatchedOnCol = EnsureOutputColumn(bankSheet, bankHeaderRow, MATCHED_ON_HEADER)

    glStatusCol = EnsureOutputColumn(glSheet, glHeaderRow, STATUS_HEADER)
    glMatchedToCol = EnsureOutputColumn(glSheet, glHeaderRow, MATCHED_TO_HEADER)
    glScoreCol = EnsureOutputColumn(glSheet, glHeaderRow, MATCH_SCORE_HEADER)
    glMatchedOnCol = EnsureOutputColumn(glSheet, glHeaderRow, MATCHED_ON_HEADER)

    ClearPreviousResults bankSheet, bankHeaderRow + 1, bankLastRow, Array(bankStatusCol, bankMatchedToCol, bankScoreCol, bankMatchedOnCol)
    ClearPreviousResults glSheet, glHeaderRow + 1, glLastRow, Array(glStatusCol, glMatchedToCol, glScoreCol, glMatchedOnCol)

    Set glBuckets = BuildAmountBuckets(glSheet, glHeaderRow + 1, glLastRow, glAmountCol)
    Set matchedGlRows = CreateObject("Scripting.Dictionary")

    For bankRow = bankHeaderRow + 1 To bankLastRow
        If HasTransactionData(bankSheet, bankRow, bankAmountCol, bankDescCol, bankRefCol) Then
            bestGlRow = 0
            bestScore = FindBestGlMatch(bankSheet, bankRow, bankDateCol, bankAmountCol, bankDescCol, bankRefCol, _
                                        glSheet, glDateCol, glAmountCol, glDescCol, glRefCol, _
                                        glBuckets, matchedGlRows, bestGlRow)

            If bestGlRow > 0 Then
                matchedCount = matchedCount + 1
                matchedGlRows(CStr(bestGlRow)) = True
                WriteMatchResult bankSheet, bankRow, bankStatusCol, bankMatchedToCol, bankScoreCol, bankMatchedOnCol, _
                    "Matched", "GL row " & bestGlRow, bestScore
                WriteMatchResult glSheet, bestGlRow, glStatusCol, glMatchedToCol, glScoreCol, glMatchedOnCol, _
                    "Matched", "Bank row " & bankRow, bestScore
            Else
                WriteMatchResult bankSheet, bankRow, bankStatusCol, bankMatchedToCol, bankScoreCol, bankMatchedOnCol, _
                    "Unmatched", "No GL match found", 0
            End If
        End If
    Next bankRow

    MarkUnmatchedGlRows glSheet, glHeaderRow + 1, glLastRow, glStatusCol, glMatchedToCol, glScoreCol, glMatchedOnCol, matchedGlRows, glAmountCol, glDescCol, glRefCol

    Set reportSheet = PrepareReportSheet(ThisWorkbook, REPORT_SHEET_NAME)
    BuildReconciliationReport reportSheet, bankSheet, glSheet, bankHeaderRow, glHeaderRow, _
        bankDateCol, bankAmountCol, bankDescCol, bankRefCol, bankStatusCol, _
        glDateCol, glAmountCol, glDescCol, glRefCol, glStatusCol, matchedCount

    MsgBox matchedCount & " transaction(s) matched." & vbCrLf & _
           "Review the '" & REPORT_SHEET_NAME & "' sheet for unmatched items and totals.", _
           vbInformation, "MacroWise"

CleanExit:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub

CleanFail:
    MsgBox "Bank reconciliation stopped: " & Err.Description, vbCritical, "MacroWise"
    Resume CleanExit
End Sub

Private Function BuildAmountBuckets(ByVal ws As Worksheet, ByVal firstRow As Long, ByVal lastRow As Long, ByVal amountCol As Long) As Object
    Dim buckets As Object
    Dim amountKey As String
    Dim rowIndex As Long
    Dim bucket As Collection

    Set buckets = CreateObject("Scripting.Dictionary")

    For rowIndex = firstRow To lastRow
        If IsNumeric(ws.Cells(rowIndex, amountCol).Value) Then
            amountKey = AmountKey(ws.Cells(rowIndex, amountCol).Value)
            If buckets.Exists(amountKey) Then
                Set bucket = buckets(amountKey)
            Else
                Set bucket = New Collection
                buckets.Add amountKey, bucket
            End If
            bucket.Add rowIndex
        End If
    Next rowIndex

    Set BuildAmountBuckets = buckets
End Function

Private Function FindBestGlMatch(ByVal bankSheet As Worksheet, ByVal bankRow As Long, _
                                 ByVal bankDateCol As Long, ByVal bankAmountCol As Long, ByVal bankDescCol As Long, ByVal bankRefCol As Long, _
                                 ByVal glSheet As Worksheet, ByVal glDateCol As Long, ByVal glAmountCol As Long, ByVal glDescCol As Long, ByVal glRefCol As Long, _
                                 ByVal glBuckets As Object, ByVal matchedGlRows As Object, ByRef bestGlRow As Long) As Long
    Dim amountKey As String
    Dim candidates As Collection
    Dim candidateRow As Variant
    Dim score As Long
    Dim bestScore As Long
    Dim availableCandidates As Long

    If Not IsNumeric(bankSheet.Cells(bankRow, bankAmountCol).Value) Then Exit Function

    amountKey = AmountKey(bankSheet.Cells(bankRow, bankAmountCol).Value)
    If Not glBuckets.Exists(amountKey) Then Exit Function

    Set candidates = glBuckets(amountKey)

    For Each candidateRow In candidates
        If Not matchedGlRows.Exists(CStr(candidateRow)) Then
            availableCandidates = availableCandidates + 1
            score = ScoreCandidate(bankSheet, bankRow, bankDateCol, bankDescCol, bankRefCol, _
                                   glSheet, CLng(candidateRow), glDateCol, glDescCol, glRefCol)
            If score > bestScore Then
                bestScore = score
                bestGlRow = CLng(candidateRow)
            End If
        End If
    Next candidateRow

    If bestScore < 15 Then
        If availableCandidates = 1 And bestGlRow > 0 Then
            bestScore = 15
        Else
            bestGlRow = 0
            bestScore = 0
        End If
    End If

    FindBestGlMatch = bestScore
End Function

Private Function ScoreCandidate(ByVal bankSheet As Worksheet, ByVal bankRow As Long, ByVal bankDateCol As Long, ByVal bankDescCol As Long, ByVal bankRefCol As Long, _
                                ByVal glSheet As Worksheet, ByVal glRow As Long, ByVal glDateCol As Long, ByVal glDescCol As Long, ByVal glRefCol As Long) As Long
    Dim bankDate As Variant
    Dim glDate As Variant
    Dim dateDifference As Long
    Dim bankDescription As String
    Dim glDescription As String
    Dim bankReference As String
    Dim glReference As String

    bankDate = bankSheet.Cells(bankRow, bankDateCol).Value
    glDate = glSheet.Cells(glRow, glDateCol).Value
    bankDescription = NormalizeText(GetCellText(bankSheet, bankRow, bankDescCol))
    glDescription = NormalizeText(GetCellText(glSheet, glRow, glDescCol))
    bankReference = NormalizeText(GetCellText(bankSheet, bankRow, bankRefCol))
    glReference = NormalizeText(GetCellText(glSheet, glRow, glRefCol))
    ScoreCandidate = 10

    If IsDate(bankDate) And IsDate(glDate) Then
        dateDifference = Abs(DateDiff("d", CDate(bankDate), CDate(glDate)))
        If dateDifference = 0 Then
            ScoreCandidate = ScoreCandidate + 35
        ElseIf dateDifference <= 3 Then
            ScoreCandidate = ScoreCandidate + (25 - (dateDifference * 5))
        ElseIf dateDifference <= 7 Then
            ScoreCandidate = ScoreCandidate + 5
        End If
    End If

    If Len(bankReference) > 0 And Len(glReference) > 0 Then
        If bankReference = glReference Then
            ScoreCandidate = ScoreCandidate + 35
        ElseIf InStr(1, glReference, bankReference, vbTextCompare) > 0 Or InStr(1, bankReference, glReference, vbTextCompare) > 0 Then
            ScoreCandidate = ScoreCandidate + 20
        End If
    End If

    If Len(bankDescription) > 0 And Len(glDescription) > 0 Then
        If bankDescription = glDescription Then
            ScoreCandidate = ScoreCandidate + 25
        ElseIf InStr(1, glDescription, bankDescription, vbTextCompare) > 0 Or InStr(1, bankDescription, glDescription, vbTextCompare) > 0 Then
            ScoreCandidate = ScoreCandidate + 15
        End If
    End If
End Function

Private Sub BuildReconciliationReport(ByVal reportSheet As Worksheet, ByVal bankSheet As Worksheet, ByVal glSheet As Worksheet, _
                                      ByVal bankHeaderRow As Long, ByVal glHeaderRow As Long, _
                                      ByVal bankDateCol As Long, ByVal bankAmountCol As Long, ByVal bankDescCol As Long, ByVal bankRefCol As Long, ByVal bankStatusCol As Long, _
                                      ByVal glDateCol As Long, ByVal glAmountCol As Long, ByVal glDescCol As Long, ByVal glRefCol As Long, ByVal glStatusCol As Long, _
                                      ByVal matchedCount As Long)
    Dim bankLastRow As Long
    Dim glLastRow As Long
    Dim bankRow As Long
    Dim glRow As Long
    Dim outputRow As Long
    Dim unmatchedBankCount As Long
    Dim unmatchedGlCount As Long
    Dim matchedAmount As Double
    Dim unmatchedBankAmount As Double
    Dim unmatchedGlAmount As Double
    Dim bankSectionTitleRow As Long
    Dim bankTableHeaderRow As Long
    Dim bankFirstDataRow As Long
    Dim bankLastDataRow As Long
    Dim glSectionTitleRow As Long
    Dim glTableHeaderRow As Long
    Dim glFirstDataRow As Long
    Dim glLastDataRow As Long

    bankLastRow = LastUsedRow(bankSheet)
    glLastRow = LastUsedRow(glSheet)

    reportSheet.Cells.Clear
    reportSheet.Cells(1, 1).Value = "Metric"
    reportSheet.Cells(1, 2).Value = "Value"

    For bankRow = bankHeaderRow + 1 To bankLastRow
        If bankSheet.Cells(bankRow, bankStatusCol).Value = "Matched" And IsNumeric(bankSheet.Cells(bankRow, bankAmountCol).Value) Then
            matchedAmount = matchedAmount + CDbl(bankSheet.Cells(bankRow, bankAmountCol).Value)
        ElseIf bankSheet.Cells(bankRow, bankStatusCol).Value = "Unmatched" And IsNumeric(bankSheet.Cells(bankRow, bankAmountCol).Value) Then
            unmatchedBankCount = unmatchedBankCount + 1
            unmatchedBankAmount = unmatchedBankAmount + CDbl(bankSheet.Cells(bankRow, bankAmountCol).Value)
        End If
    Next bankRow

    For glRow = glHeaderRow + 1 To glLastRow
        If glSheet.Cells(glRow, glStatusCol).Value = "Unmatched" And IsNumeric(glSheet.Cells(glRow, glAmountCol).Value) Then
            unmatchedGlCount = unmatchedGlCount + 1
            unmatchedGlAmount = unmatchedGlAmount + CDbl(glSheet.Cells(glRow, glAmountCol).Value)
        End If
    Next glRow

    reportSheet.Cells(2, 1).Value = "Matched Transactions"
    reportSheet.Cells(2, 2).Value = matchedCount
    reportSheet.Cells(3, 1).Value = "Matched Amount"
    reportSheet.Cells(3, 2).Value = matchedAmount
    reportSheet.Cells(4, 1).Value = "Unmatched Bank Items"
    reportSheet.Cells(4, 2).Value = unmatchedBankCount
    reportSheet.Cells(5, 1).Value = "Unmatched Bank Amount"
    reportSheet.Cells(5, 2).Value = unmatchedBankAmount
    reportSheet.Cells(6, 1).Value = "Unmatched GL Items"
    reportSheet.Cells(6, 2).Value = unmatchedGlCount
    reportSheet.Cells(7, 1).Value = "Unmatched GL Amount"
    reportSheet.Cells(7, 2).Value = unmatchedGlAmount

    With reportSheet.Range("A1:B7")
        .Font.Bold = False
        .Borders.LineStyle = xlContinuous
    End With
    reportSheet.Range("A1:B1").Font.Bold = True
    reportSheet.Range("B2").NumberFormat = "0"
    reportSheet.Range("B3").NumberFormat = "#,##0.00_);(#,##0.00)"
    reportSheet.Range("B4").NumberFormat = "0"
    reportSheet.Range("B5").NumberFormat = "#,##0.00_);(#,##0.00)"
    reportSheet.Range("B6").NumberFormat = "0"
    reportSheet.Range("B7").NumberFormat = "#,##0.00_);(#,##0.00)"

    bankSectionTitleRow = 10
    bankTableHeaderRow = 11
    bankFirstDataRow = 12

    reportSheet.Cells(bankSectionTitleRow, 1).Value = "Unmatched Bank Statement Items"
    reportSheet.Cells(bankTableHeaderRow, 1).Value = "Row"
    reportSheet.Cells(bankTableHeaderRow, 2).Value = "Date"
    reportSheet.Cells(bankTableHeaderRow, 3).Value = "Description"
    reportSheet.Cells(bankTableHeaderRow, 4).Value = "Reference"
    reportSheet.Cells(bankTableHeaderRow, 5).Value = "Amount"
    reportSheet.Range(reportSheet.Cells(bankSectionTitleRow, 1), reportSheet.Cells(bankSectionTitleRow, 5)).Font.Bold = True
    reportSheet.Range(reportSheet.Cells(bankTableHeaderRow, 1), reportSheet.Cells(bankTableHeaderRow, 5)).Font.Bold = True

    outputRow = bankFirstDataRow
    For bankRow = bankHeaderRow + 1 To bankLastRow
        If bankSheet.Cells(bankRow, bankStatusCol).Value = "Unmatched" Then
            reportSheet.Cells(outputRow, 1).Value = bankRow
            reportSheet.Cells(outputRow, 2).Value = bankSheet.Cells(bankRow, bankDateCol).Value
            reportSheet.Cells(outputRow, 3).Value = GetCellText(bankSheet, bankRow, bankDescCol)
            reportSheet.Cells(outputRow, 4).Value = GetCellText(bankSheet, bankRow, bankRefCol)
            reportSheet.Cells(outputRow, 5).Value = bankSheet.Cells(bankRow, bankAmountCol).Value
            outputRow = outputRow + 1
        End If
    Next bankRow
    bankLastDataRow = outputRow - 1

    glSectionTitleRow = outputRow + 2
    glTableHeaderRow = glSectionTitleRow + 1
    glFirstDataRow = glSectionTitleRow + 2

    reportSheet.Cells(glSectionTitleRow, 1).Value = "Unmatched General Ledger Items"
    reportSheet.Cells(glTableHeaderRow, 1).Value = "Row"
    reportSheet.Cells(glTableHeaderRow, 2).Value = "Date"
    reportSheet.Cells(glTableHeaderRow, 3).Value = "Description"
    reportSheet.Cells(glTableHeaderRow, 4).Value = "Reference"
    reportSheet.Cells(glTableHeaderRow, 5).Value = "Amount"
    reportSheet.Range(reportSheet.Cells(glSectionTitleRow, 1), reportSheet.Cells(glSectionTitleRow, 5)).Font.Bold = True
    reportSheet.Range(reportSheet.Cells(glTableHeaderRow, 1), reportSheet.Cells(glTableHeaderRow, 5)).Font.Bold = True

    outputRow = glFirstDataRow
    For glRow = glHeaderRow + 1 To glLastRow
        If glSheet.Cells(glRow, glStatusCol).Value = "Unmatched" Then
            reportSheet.Cells(outputRow, 1).Value = glRow
            reportSheet.Cells(outputRow, 2).Value = glSheet.Cells(glRow, glDateCol).Value
            reportSheet.Cells(outputRow, 3).Value = GetCellText(glSheet, glRow, glDescCol)
            reportSheet.Cells(outputRow, 4).Value = GetCellText(glSheet, glRow, glRefCol)
            reportSheet.Cells(outputRow, 5).Value = glSheet.Cells(glRow, glAmountCol).Value
            outputRow = outputRow + 1
        End If
    Next glRow
    glLastDataRow = outputRow - 1

    reportSheet.Columns("A:E").AutoFit
    If bankLastDataRow >= bankFirstDataRow Then
        reportSheet.Range("B" & bankFirstDataRow & ":B" & bankLastDataRow).NumberFormat = "dd-mmm-yyyy"
        reportSheet.Range("E" & bankFirstDataRow & ":E" & bankLastDataRow).NumberFormat = "#,##0.00_);(#,##0.00)"
    End If
    If glLastDataRow >= glFirstDataRow Then
        reportSheet.Range("B" & glFirstDataRow & ":B" & glLastDataRow).NumberFormat = "dd-mmm-yyyy"
        reportSheet.Range("E" & glFirstDataRow & ":E" & glLastDataRow).NumberFormat = "#,##0.00_);(#,##0.00)"
    End If
End Sub

Private Sub MarkUnmatchedGlRows(ByVal ws As Worksheet, ByVal firstRow As Long, ByVal lastRow As Long, _
                                ByVal statusCol As Long, ByVal matchedToCol As Long, ByVal scoreCol As Long, ByVal matchedOnCol As Long, _
                                ByVal matchedRows As Object, ByVal amountCol As Long, ByVal descCol As Long, ByVal refCol As Long)
    Dim rowIndex As Long

    For rowIndex = firstRow To lastRow
        If HasTransactionData(ws, rowIndex, amountCol, descCol, refCol) Then
            If Not matchedRows.Exists(CStr(rowIndex)) Then
                WriteMatchResult ws, rowIndex, statusCol, matchedToCol, scoreCol, matchedOnCol, "Unmatched", "No bank match found", 0
            End If
        End If
    Next rowIndex
End Sub

Private Sub WriteMatchResult(ByVal ws As Worksheet, ByVal rowIndex As Long, ByVal statusCol As Long, ByVal matchedToCol As Long, _
                             ByVal scoreCol As Long, ByVal matchedOnCol As Long, ByVal statusText As String, ByVal matchedTo As String, ByVal scoreValue As Long)
    With ws
        .Cells(rowIndex, statusCol).Value = statusText
        .Cells(rowIndex, matchedToCol).Value = matchedTo
        .Cells(rowIndex, scoreCol).Value = scoreValue
        .Cells(rowIndex, matchedOnCol).Value = Now

        If statusText = "Matched" Then
            .Range(.Cells(rowIndex, statusCol), .Cells(rowIndex, matchedOnCol)).Interior.Color = RGB(228, 240, 235)
            .Cells(rowIndex, statusCol).Font.Color = RGB(45, 90, 61)
        Else
            .Range(.Cells(rowIndex, statusCol), .Cells(rowIndex, matchedOnCol)).Interior.Color = RGB(255, 235, 235)
            .Cells(rowIndex, statusCol).Font.Color = RGB(156, 0, 6)
        End If
    End With
End Sub

Private Sub ClearPreviousResults(ByVal ws As Worksheet, ByVal firstRow As Long, ByVal lastRow As Long, ByVal columnList As Variant)
    Dim columnIndex As Variant

    If lastRow < firstRow Then Exit Sub

    For Each columnIndex In columnList
        ws.Range(ws.Cells(firstRow, CLng(columnIndex)), ws.Cells(lastRow, CLng(columnIndex))).ClearContents
        ws.Range(ws.Cells(firstRow, CLng(columnIndex)), ws.Cells(lastRow, CLng(columnIndex))).Interior.ColorIndex = xlNone
        ws.Range(ws.Cells(firstRow, CLng(columnIndex)), ws.Cells(lastRow, CLng(columnIndex))).Font.Color = RGB(26, 26, 26)
    Next columnIndex
End Sub

Private Function EnsureOutputColumn(ByVal ws As Worksheet, ByVal headerRow As Long, ByVal headerName As String) As Long
    Dim lastCol As Long
    Dim columnIndex As Long

    lastCol = LastUsedColumn(ws)

    For columnIndex = 1 To lastCol
        If StrComp(Trim$(CStr(ws.Cells(headerRow, columnIndex).Value)), headerName, vbTextCompare) = 0 Then
            EnsureOutputColumn = columnIndex
            Exit Function
        End If
    Next columnIndex

    EnsureOutputColumn = lastCol + 1
    ws.Cells(headerRow, EnsureOutputColumn).Value = headerName
    ws.Cells(headerRow, EnsureOutputColumn).Font.Bold = True
End Function

Private Function PrepareReportSheet(ByVal wb As Workbook, ByVal sheetName As String) As Worksheet
    Dim ws As Worksheet

    On Error Resume Next
    Set ws = wb.Worksheets(sheetName)
    On Error GoTo 0

    If ws Is Nothing Then
        Set ws = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
        ws.Name = sheetName
    Else
        ws.Cells.Clear
    End If

    Set PrepareReportSheet = ws
End Function

Private Function RequireWorksheet(ByVal wb As Workbook, ByVal sheetName As String) As Worksheet
    On Error Resume Next
    Set RequireWorksheet = wb.Worksheets(sheetName)
    On Error GoTo 0

    If RequireWorksheet Is Nothing Then
        Err.Raise vbObjectError + 513, "MacroWise", "Worksheet '" & sheetName & "' was not found."
    End If
End Function

Private Sub ValidateCoreColumns(ByVal dateCol As Long, ByVal amountCol As Long, ByVal sheetName As String)
    If dateCol = 0 Or amountCol = 0 Then
        Err.Raise vbObjectError + 514, "MacroWise", _
            "Required Date and Amount columns could not be detected on '" & sheetName & "'."
    End If
End Sub

Private Function FindColumn(ByVal ws As Worksheet, ByVal headerRow As Long, ByVal possibleHeaders As Variant) As Long
    Dim lastCol As Long
    Dim columnIndex As Long
    Dim headerText As String
    Dim possibleHeader As Variant

    lastCol = LastUsedColumn(ws)

    For columnIndex = 1 To lastCol
        headerText = NormalizeText(CStr(ws.Cells(headerRow, columnIndex).Value))
        For Each possibleHeader In possibleHeaders
            If headerText = NormalizeText(CStr(possibleHeader)) Then
                FindColumn = columnIndex
                Exit Function
            End If
            If InStr(1, headerText, NormalizeText(CStr(possibleHeader)), vbTextCompare) > 0 Then
                FindColumn = columnIndex
                Exit Function
            End If
        Next possibleHeader
    Next columnIndex
End Function

Private Function HasTransactionData(ByVal ws As Worksheet, ByVal rowIndex As Long, ByVal amountCol As Long, ByVal descCol As Long, ByVal refCol As Long) As Boolean
    If amountCol > 0 Then
        If Len(Trim$(CStr(ws.Cells(rowIndex, amountCol).Value))) > 0 Then
            HasTransactionData = True
            Exit Function
        End If
    End If

    If descCol > 0 Then
        If Len(Trim$(CStr(ws.Cells(rowIndex, descCol).Value))) > 0 Then
            HasTransactionData = True
            Exit Function
        End If
    End If

    If refCol > 0 Then
        If Len(Trim$(CStr(ws.Cells(rowIndex, refCol).Value))) > 0 Then
            HasTransactionData = True
        End If
    End If
End Function

Private Function DetectHeaderRow(ByVal ws As Worksheet, ByVal searchRows As Long) As Long
    Dim rowIndex As Long
    Dim populatedCells As Long
    Dim bestCount As Long
    Dim bestRow As Long

    For rowIndex = 1 To WorksheetFunction.Min(searchRows, ws.Rows.Count)
        populatedCells = WorksheetFunction.CountA(ws.Rows(rowIndex))
        If populatedCells > bestCount Then
            bestCount = populatedCells
            bestRow = rowIndex
        End If
    Next rowIndex

    DetectHeaderRow = bestRow
End Function

Private Function LastUsedRow(ByVal ws As Worksheet) As Long
    Dim lastCell As Range

    On Error Resume Next
    Set lastCell = ws.Cells.Find(What:="*", After:=ws.Range("A1"), LookIn:=xlFormulas, _
                                 SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    On Error GoTo 0

    If lastCell Is Nothing Then
        LastUsedRow = 0
    Else
        LastUsedRow = lastCell.Row
    End If
End Function

Private Function LastUsedColumn(ByVal ws As Worksheet) As Long
    Dim lastCell As Range

    On Error Resume Next
    Set lastCell = ws.Cells.Find(What:="*", After:=ws.Range("A1"), LookIn:=xlFormulas, _
                                 SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
    On Error GoTo 0

    If lastCell Is Nothing Then
        LastUsedColumn = 0
    Else
        LastUsedColumn = lastCell.Column
    End If
End Function

Private Function AmountKey(ByVal amountValue As Variant) As String
    AmountKey = Format$(Round(CDbl(amountValue), 2), "0.00")
End Function

Private Function GetCellText(ByVal ws As Worksheet, ByVal rowIndex As Long, ByVal columnIndex As Long) As String
    If columnIndex = 0 Then
        GetCellText = ""
    Else
        GetCellText = Trim$(CStr(ws.Cells(rowIndex, columnIndex).Value))
    End If
End Function

Private Function NormalizeText(ByVal rawText As String) As String
    rawText = Trim$(LCase$(rawText))
    rawText = Replace(rawText, "-", " ")
    rawText = Replace(rawText, "_", " ")
    rawText = Replace(rawText, ".", " ")
    rawText = WorksheetFunction.Trim(rawText)
    NormalizeText = rawText
End Function