Attribute VB_Name = "ConsolidateReport"
'==============================================================================
' Sample Retail Co Trade Supplies: one-click consolidation of monthly branch files
' Sample project with placeholder data, not a real client.
'
' ConsolidateMonthlyFiles
'   1. Reads every file matching the pattern in the folder set on the Control sheet
'   2. Finds the header row in each file and maps the columns by name, so the
'      branches' different layouts and column orders all work
'   3. Skips Excel lock files (~$...), duplicate branch + month files and
'      "Total" rows, and converts text dates
'   4. Writes all lines to the Data sheet, with the source file and row for each
'   5. Rebuilds the Report sheet (live SUMIFS formulas) and writes a Log sheet
'      with one line per file, including warnings
' Source files are opened read-only and never changed.
'==============================================================================
Option Explicit

Private Const CONTROL_SHEET As String = "Control"
Private Const DATA_SHEET As String = "Data"
Private Const REPORT_SHEET As String = "Report"
Private Const LOG_SHEET As String = "Log"
Private Const N_STD As Long = 7          ' Date, Invoice, Customer, Product group, Qty, Net sales, Cost
Private Const N_OUT As Long = 12         ' columns written to the Data sheet
Private Const HEADER_SCAN_ROWS As Long = 15

Public Sub ConsolidateMonthlyFiles()
    Dim wb As Object, wsC As Object, wsD As Object, wsL As Object
    Dim folder As String, pattern As String, f As String
    Dim files() As String, nFiles As Long, i As Long
    Dim doneKeys As String, firstFile As String
    Dim dataRow As Long, logRow As Long, nImported As Long, nWarn As Long, nSkipped As Long
    Dim t0 As Double

    t0 = Timer
    Set wb = ThisWorkbook
    Set wsC = wb.Worksheets(CONTROL_SHEET)
    Set wsD = wb.Worksheets(DATA_SHEET)
    Set wsL = wb.Worksheets(LOG_SHEET)

    folder = Trim$(CStr(wsC.Range("C5").Value))
    If folder = "" Then folder = wb.Path & Application.PathSeparator & "monthly-files"
    If Right$(folder, 1) <> Application.PathSeparator Then folder = folder & Application.PathSeparator
    pattern = Trim$(CStr(wsC.Range("C6").Value))
    If pattern = "" Then pattern = "Branch_*_*.xlsx"

    ' ---- collect and sort the file names ----
    nFiles = 0
    f = Dir(folder & pattern)
    Do While f <> ""
        nFiles = nFiles + 1
        ReDim Preserve files(1 To nFiles)
        files(nFiles) = f
        f = Dir()
    Loop
    If nFiles = 0 Then
        MsgBox "No files matching " & pattern & " in" & vbCrLf & folder, vbExclamation, "Consolidate"
        Exit Sub
    End If
    SortFiles files

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error GoTo Fail

    ' ---- reset output sheets ----
    wsD.Cells.ClearContents
    wsD.Range("A1:L1").Value = Array("Branch", "Month", "Date", "Invoice", "Customer", "Product group", _
                                     "Qty", "Net sales", "Cost", "Gross profit", "Source file", "Source row")
    wsL.Cells.ClearContents
    wsL.Range("A1:H1").Value = Array("File", "Branch", "Month", "Header row", "Lines imported", "Net sales", "Status", "Notes")
    dataRow = 2: logRow = 2: doneKeys = "|"

    For i = 1 To nFiles
        Dim branch As String, monthKey As String, monthStart As Date, key As String, status As String, notes As String
        Dim nLines As Long, netSum As Double, hdrRow As Long
        f = files(i): nLines = 0: netSum = 0: hdrRow = 0: notes = ""

        If Left$(f, 2) = "~$" Then
            status = "Skipped": notes = "Excel lock file (someone has the file open)"
            nSkipped = nSkipped + 1
        ElseIf Not ParseFileName(f, branch, monthKey, monthStart) Then
            status = "Skipped": notes = "Name does not match Branch_<Branch>_<YYYY-MM>.xlsx"
            nSkipped = nSkipped + 1
        Else
            key = "|" & LCase$(branch) & "_" & monthKey & "|"
            If InStr(1, doneKeys, key, vbTextCompare) > 0 Then
                status = "Skipped": notes = "Duplicate: " & branch & " " & monthKey & " already imported from " & FirstFileFor(wsL, branch, monthKey)
                nSkipped = nSkipped + 1
            Else
                status = ImportFile(folder & f, f, branch, monthStart, wsD, dataRow, nLines, netSum, hdrRow, notes)
                If status <> "Error" Then
                    doneKeys = doneKeys & Mid$(key, 2)
                    nImported = nImported + 1
                    If status = "Imported with warnings" Then nWarn = nWarn + 1
                End If
            End If
        End If

        wsL.Cells(logRow, 1).Value = f
        If status <> "Skipped" Or branch <> "" Then wsL.Cells(logRow, 2).Value = branch
        If monthKey <> "" Then wsL.Cells(logRow, 3).Value = CDbl(monthStart)
        If hdrRow > 0 Then wsL.Cells(logRow, 4).Value = hdrRow
        If status <> "Skipped" Then wsL.Cells(logRow, 5).Value = nLines: wsL.Cells(logRow, 6).Value = netSum
        wsL.Cells(logRow, 7).Value = status
        wsL.Cells(logRow, 8).Value = notes
        logRow = logRow + 1
        branch = "": monthKey = ""
    Next i

    BuildReport wb, dataRow - 1, nImported, nWarn
    FormatOutputSheets wsD, wsL, dataRow - 1, logRow - 1

    wsC.Range("C9").Value = CDbl(Now)
    wsC.Range("C10").Value = nImported
    wsC.Range("C11").Value = nSkipped
    wsC.Range("C12").Value = dataRow - 2
    wsC.Range("C13").Value = nWarn
    wsC.Range("C14").Value = Round(Timer - t0, 1)

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    wb.Worksheets(REPORT_SHEET).Activate
    If UCase$(Trim$(CStr(wsC.Range("C7").Value))) <> "NO" Then
        MsgBox nImported & " files imported (" & dataRow - 2 & " lines), " & nSkipped & " skipped, " & _
               nWarn & " with warnings. See the Log sheet.", vbInformation, "Consolidate"
    End If
    Exit Sub

Fail:
    wsC.Range("C9").Value = "Stopped on " & f & ": " & Err.Description
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    MsgBox "Stopped on " & f & ": " & Err.Description, vbCritical, "Consolidate"
End Sub

' Opens one branch file read-only, maps its columns and appends its lines to Data.
Private Function ImportFile(ByVal fullPath As String, ByVal fileName As String, ByVal branch As String, _
        ByVal monthStart As Date, ByVal wsD As Object, ByRef dataRow As Long, ByRef nLines As Long, _
        ByRef netSum As Double, ByRef hdrRow As Long, ByRef notes As String) As String
    Dim src As Object, v As Variant, firstRow As Long, r As Long, c As Long, k As Long
    Dim colOf(1 To N_STD) As Long, out() As Variant, n As Long, missing As Long, outside As Long, totals As Long
    Dim d As Variant, net As Variant, cost As Variant

    Set src = Workbooks.Open(fullPath, False, True)       ' UpdateLinks:=False, ReadOnly:=True
    v = src.Worksheets(1).UsedRange.Value
    firstRow = src.Worksheets(1).UsedRange.Row
    src.Close False

    ' find the header row: the first row where all 7 standard columns can be mapped
    For r = 1 To Application.Min(HEADER_SCAN_ROWS, UBound(v, 1))
        For k = 1 To N_STD: colOf(k) = 0: Next k
        For c = 1 To UBound(v, 2)
            k = StdColumn(CStr(v(r, c)))
            If k > 0 Then If colOf(k) = 0 Then colOf(k) = c
        Next c
        If AllMapped(colOf) Then hdrRow = r + firstRow - 1: Exit For
    Next r
    If hdrRow = 0 Then
        notes = "No header row with Date, Invoice, Customer, Product group, Qty, Net sales and Cost in the first " & HEADER_SCAN_ROWS & " rows"
        ImportFile = "Error": Exit Function
    End If

    ReDim out(1 To UBound(v, 1), 1 To N_OUT)
    For r = hdrRow - firstRow + 2 To UBound(v, 1)
        If IsTotalRow(v, r) Then
            totals = totals + 1
        ElseIf Trim$(CStr(v(r, colOf(2)))) <> "" Then
            n = n + 1
            d = ToDate(v(r, colOf(1)))
            net = v(r, colOf(6)): cost = v(r, colOf(7))
            out(n, 1) = branch
            out(n, 2) = CDbl(monthStart)                 ' dates are written as serial numbers + a date format
            If IsDate(d) Then out(n, 3) = CDbl(CDate(d)) Else out(n, 3) = d
            out(n, 4) = v(r, colOf(2))
            out(n, 5) = v(r, colOf(3))
            out(n, 6) = v(r, colOf(4))
            out(n, 7) = v(r, colOf(5))
            If IsNumeric(net) And Trim$(CStr(net)) <> "" Then
                out(n, 8) = CDbl(net): netSum = netSum + CDbl(net)
                If IsNumeric(cost) And Trim$(CStr(cost)) <> "" Then out(n, 10) = CDbl(net) - CDbl(cost)
            Else
                missing = missing + 1
            End If
            If IsNumeric(cost) And Trim$(CStr(cost)) <> "" Then out(n, 9) = CDbl(cost)
            out(n, 11) = fileName
            out(n, 12) = r + firstRow - 1
            If IsDate(d) Then
                If Year(d) <> Year(monthStart) Or Month(d) <> Month(monthStart) Then outside = outside + 1
            End If
        End If
    Next r

    If n > 0 Then
        wsD.Cells(dataRow, 1).Resize(n, N_OUT).Value = SliceRows(out, n)
        dataRow = dataRow + n
    End If
    nLines = n
    notes = "Header on row " & hdrRow
    If totals > 0 Then notes = notes & "; " & totals & " total row skipped"
    ImportFile = "Imported"
    If missing > 0 Then notes = notes & "; " & missing & " row(s) with no net sales value": ImportFile = "Imported with warnings"
    If outside > 0 Then notes = notes & "; " & outside & " row(s) dated outside " & Format$(monthStart, "mmm yyyy"): ImportFile = "Imported with warnings"
End Function

' Writes the Report sheet: KPIs plus branch x month tables built with SUMIFS on the Data sheet.
Private Sub BuildReport(ByVal wb As Object, ByVal lastRow As Long, ByVal nFiles As Long, ByVal nWarn As Long)
    Dim ws As Object, wsD As Object, branches() As String, months() As Date, groups() As String
    Dim nB As Long, nM As Long, nG As Long, i As Long, j As Long, r As Long
    Dim colTot As Long, colShare As Long, colChg As Long, top As Long

    Set ws = wb.Worksheets(REPORT_SHEET)
    Set wsD = wb.Worksheets(DATA_SHEET)
    ws.Cells.Clear
    UniqueStrings wsD, 1, lastRow, branches, nB
    UniqueStrings wsD, 6, lastRow, groups, nG
    UniqueMonths wsD, lastRow, months, nM
    Dim cBranch As String, cMonth As String, cGroup As String, cNet As String, cGP As String

    cBranch = DataCol("A", lastRow)
    cMonth = DataCol("B", lastRow)
    cGroup = DataCol("F", lastRow)
    cNet = DataCol("H", lastRow)
    cGP = DataCol("J", lastRow)

    ws.Range("B1").Value = "Sample Retail Co Trade Supplies: branch sales report, " & Format$(months(1), "mmm") & " to " & Format$(months(nM), "mmm yyyy")
    ws.Range("B2").Value = "Built by ConsolidateMonthlyFiles from " & nFiles & " monthly files. Sample project with placeholder data, not a real client."

    ' KPI row
    ws.Range("B4:G4").Value = Array("Net sales", "Gross profit", "Gross margin", "Invoice lines", "Files imported", "Files with warnings")
    ws.Range("B5").Formula = "=SUM(" & cNet & ")"
    ws.Range("C5").Formula = "=SUM(" & cGP & ")"
    ws.Range("D5").Formula = "=IF(B5=0,0,C5/B5)"
    ws.Range("E5").Formula = "=COUNTA(" & DataCol("D", lastRow) & ")"
    ws.Range("F5").Value = nFiles
    ws.Range("G5").Value = nWarn

    ' Table 1: net sales by branch x month
    colTot = 3 + nM: colShare = colTot + 1: colChg = colTot + 2
    top = 7
    ws.Cells(top, 2).Value = "Net sales by branch"
    HeaderRow ws, top + 1, months, nM, "Branch", "Total", "Share", "vs last month"
    For i = 1 To nB
        r = top + 1 + i
        ws.Cells(r, 2).Value = branches(i)
        For j = 1 To nM
            ws.Cells(r, 2 + j).Formula = "=SUMIFS(" & cNet & "," & cBranch & ",$B" & r & "," & cMonth & "," & ws.Cells(top + 1, 2 + j).Address & ")"
        Next j
        ws.Cells(r, colTot).Formula = "=SUM(" & ws.Cells(r, 3).Address(False, False) & ":" & ws.Cells(r, 2 + nM).Address(False, False) & ")"
        ws.Cells(r, colShare).Formula = "=IF(" & ws.Cells(top + 2 + nB, colTot).Address & "=0,0," & ws.Cells(r, colTot).Address(False, False) & "/" & ws.Cells(top + 2 + nB, colTot).Address & ")"
        If nM > 1 Then ws.Cells(r, colChg).Formula = "=IF(" & ws.Cells(r, 1 + nM).Address(False, False) & "=0,0," & ws.Cells(r, 2 + nM).Address(False, False) & "/" & ws.Cells(r, 1 + nM).Address(False, False) & "-1)"
    Next i
    TotalRow ws, top + 2 + nB, top + 2, top + 1 + nB, 3, colShare, nM, colChg
    FormatTable ws, top + 1, top + 2 + nB, 2, colChg, "#,##0", colShare, colChg

    ' Table 2: gross margin % by branch x month
    top = top + nB + 5
    ws.Cells(top, 2).Value = "Gross margin % by branch"
    HeaderRow ws, top + 1, months, nM, "Branch", "Total", "", "vs last month"
    For i = 1 To nB + 1
        r = top + 1 + i
        If i <= nB Then ws.Cells(r, 2).Value = branches(i) Else ws.Cells(r, 2).Value = "All branches"
        For j = 1 To nM + 1
            Dim crit As String
            If i <= nB Then crit = "," & cBranch & ",$B" & r Else crit = ""
            If j <= nM Then crit = crit & "," & cMonth & "," & ws.Cells(top + 1, 2 + j).Address
            If crit = "" Then
                ws.Cells(r, 2 + j).Formula = "=IF(SUM(" & cNet & ")=0,0,SUM(" & cGP & ")/SUM(" & cNet & "))"
            Else
                ws.Cells(r, 2 + j).Formula = "=IFERROR(SUMIFS(" & cGP & crit & ")/SUMIFS(" & cNet & crit & "),0)"
            End If
        Next j
        If nM > 1 Then ws.Cells(r, colChg).Formula = "=(" & ws.Cells(r, 2 + nM).Address(False, False) & "-" & ws.Cells(r, 1 + nM).Address(False, False) & ")*100"
    Next i
    FormatTable ws, top + 1, top + 2 + nB, 2, colChg, "0.0%", 0, colChg
    ws.Range(ws.Cells(top + 2 + nB, 2), ws.Cells(top + 2 + nB, colChg)).Font.Bold = True
    ws.Range(ws.Cells(top + 2, colChg), ws.Cells(top + 2 + nB, colChg)).NumberFormat = "+0.0"" pts"";[Red]-0.0"" pts"""

    ' Table 3: net sales by product group x month
    top = top + nB + 5
    ws.Cells(top, 2).Value = "Net sales by product group"
    HeaderRow ws, top + 1, months, nM, "Product group", "Total", "Share", "vs last month"
    For i = 1 To nG
        r = top + 1 + i
        ws.Cells(r, 2).Value = groups(i)
        For j = 1 To nM
            ws.Cells(r, 2 + j).Formula = "=SUMIFS(" & cNet & "," & cGroup & ",$B" & r & "," & cMonth & "," & ws.Cells(top + 1, 2 + j).Address & ")"
        Next j
        ws.Cells(r, colTot).Formula = "=SUM(" & ws.Cells(r, 3).Address(False, False) & ":" & ws.Cells(r, 2 + nM).Address(False, False) & ")"
        ws.Cells(r, colShare).Formula = "=IF(" & ws.Cells(top + 2 + nG, colTot).Address & "=0,0," & ws.Cells(r, colTot).Address(False, False) & "/" & ws.Cells(top + 2 + nG, colTot).Address & ")"
        If nM > 1 Then ws.Cells(r, colChg).Formula = "=IF(" & ws.Cells(r, 1 + nM).Address(False, False) & "=0,0," & ws.Cells(r, 2 + nM).Address(False, False) & "/" & ws.Cells(r, 1 + nM).Address(False, False) & "-1)"
    Next i
    TotalRow ws, top + 2 + nG, top + 2, top + 1 + nG, 3, colShare, nM, colChg
    FormatTable ws, top + 1, top + 2 + nG, 2, colChg, "#,##0", colShare, colChg

    ' check cell: report total must equal the Data sheet total
    ws.Cells(top + nG + 4, 2).Value = "Check: report total = Data sheet total"
    ws.Cells(top + nG + 4, colTot).Formula = "=IF(ROUND(" & ws.Cells(9 + nB, colTot).Address & "-SUM(" & cNet & "),2)=0,""OK"",""CHECK"")"
    ws.Cells(top + nG + 4, colTot).Font.Bold = True

    ' KPI formatting and layout
    With ws
        .Range("B1").Font.Size = 16: .Range("B1").Font.Bold = True: .Range("B1").Font.Color = RGB(31, 56, 100)
        .Range("B2").Font.Italic = True: .Range("B2").Font.Color = RGB(89, 89, 89)
        .Range("B4:G4").Font.Bold = True: .Range("B4:G4").Font.Color = RGB(89, 89, 89)
        .Range("B5:G5").Font.Size = 14: .Range("B5:G5").Font.Bold = True: .Range("B5:G5").Font.Color = RGB(31, 56, 100)
        .Range("B4:G5").Interior.Color = RGB(237, 242, 249)
        .Range("B5:C5").NumberFormat = "$#,##0": .Range("D5").NumberFormat = "0.0%": .Range("E5:G5").NumberFormat = "#,##0"
        .Columns(1).ColumnWidth = 2
        .Columns(2).ColumnWidth = 22
        For j = 3 To colChg: .Columns(j).ColumnWidth = 13: Next j
    End With
End Sub

Private Sub HeaderRow(ByVal ws As Object, ByVal r As Long, months() As Date, ByVal nM As Long, _
        ByVal firstLabel As String, ByVal totLabel As String, ByVal shareLabel As String, ByVal chgLabel As String)
    Dim j As Long
    ws.Cells(r, 2).Value = firstLabel
    For j = 1 To nM
        ws.Cells(r, 2 + j).Value = CDbl(months(j))
        ws.Cells(r, 2 + j).NumberFormat = "mmm yyyy"
    Next j
    ws.Cells(r, 3 + nM).Value = totLabel
    ws.Cells(r, 4 + nM).Value = shareLabel
    ws.Cells(r, 5 + nM).Value = chgLabel
    ws.Cells(r - 1, 2).Font.Bold = True: ws.Cells(r - 1, 2).Font.Size = 12: ws.Cells(r - 1, 2).Font.Color = RGB(31, 56, 100)
End Sub

Private Sub TotalRow(ByVal ws As Object, ByVal r As Long, ByVal r1 As Long, ByVal r2 As Long, ByVal c1 As Long, _
        ByVal colShare As Long, ByVal nM As Long, ByVal colChg As Long)
    Dim c As Long
    ws.Cells(r, 2).Value = "Total"
    For c = c1 To colShare - 1
        ws.Cells(r, c).Formula = "=SUM(" & ws.Cells(r1, c).Address(False, False) & ":" & ws.Cells(r2, c).Address(False, False) & ")"
    Next c
    ws.Cells(r, colShare).Formula = "=IF(" & ws.Cells(r, colShare - 1).Address(False, False) & "=0,0,1)"
    If nM > 1 Then ws.Cells(r, colChg).Formula = "=IF(" & ws.Cells(r, 1 + nM).Address(False, False) & "=0,0," & ws.Cells(r, 2 + nM).Address(False, False) & "/" & ws.Cells(r, 1 + nM).Address(False, False) & "-1)"
    ws.Range(ws.Cells(r, 2), ws.Cells(r, colChg)).Font.Bold = True
    ws.Range(ws.Cells(r, 2), ws.Cells(r, colChg)).Interior.Color = RGB(221, 235, 247)
End Sub

Private Sub FormatTable(ByVal ws As Object, ByVal hdr As Long, ByVal lastR As Long, ByVal c1 As Long, ByVal c2 As Long, _
        ByVal numFmt As String, ByVal colShare As Long, ByVal colChg As Long)
    With ws.Range(ws.Cells(hdr, c1), ws.Cells(hdr, c2))
        .Font.Bold = True
        .Font.Color = RGB(255, 255, 255)
        .Interior.Color = RGB(31, 56, 100)
        .HorizontalAlignment = -4152      ' xlRight
    End With
    ws.Cells(hdr, c1).HorizontalAlignment = -4131   ' xlLeft
    ws.Range(ws.Cells(hdr + 1, c1 + 1), ws.Cells(lastR, c2)).NumberFormat = numFmt
    If colShare > 0 Then ws.Range(ws.Cells(hdr + 1, colShare), ws.Cells(lastR, colShare)).NumberFormat = "0.0%"
    ws.Range(ws.Cells(hdr + 1, colChg), ws.Cells(lastR, colChg)).NumberFormat = "+0.0%;[Red]-0.0%"
    ws.Range(ws.Cells(lastR, c1), ws.Cells(lastR, c2)).Borders(8).LineStyle = 1   ' xlEdgeTop, xlContinuous
    If colShare > 0 Then ws.Cells(lastR, colShare).NumberFormat = "0.0%"
End Sub

Private Sub FormatOutputSheets(ByVal wsD As Object, ByVal wsL As Object, ByVal lastD As Long, ByVal lastL As Long)
    With wsD
        .Range("A1:L1").Font.Bold = True: .Range("A1:L1").Interior.Color = RGB(31, 56, 100): .Range("A1:L1").Font.Color = RGB(255, 255, 255)
        .Range("B2:B" & lastD).NumberFormat = "mmm yyyy"
        .Range("C2:C" & lastD).NumberFormat = "dd/mm/yyyy"
        .Range("H2:J" & lastD).NumberFormat = "#,##0.00"
        SetWidths wsD, Array(11, 10, 11, 11, 24, 18, 7, 11, 11, 12, 36, 10)
    End With
    With wsL
        .Range("A1:H1").Font.Bold = True: .Range("A1:H1").Interior.Color = RGB(31, 56, 100): .Range("A1:H1").Font.ColorIndex = 2
        .Range("C2:C" & lastL).NumberFormat = "mmm yyyy"
        .Range("F2:F" & lastL).NumberFormat = "#,##0.00"
        Dim r As Long
        For r = 2 To lastL
            Select Case .Cells(r, 7).Value
                Case "Imported": .Cells(r, 7).Interior.Color = RGB(226, 239, 218)
                Case "Imported with warnings": .Cells(r, 7).Interior.Color = RGB(255, 242, 204)
                Case Else: .Cells(r, 7).Interior.Color = RGB(252, 228, 214)
            End Select
        Next r
        SetWidths wsL, Array(36, 12, 10, 11, 14, 12, 22, 72)
    End With
End Sub

' ---------- helpers ----------

Private Sub SetWidths(ByVal ws As Object, ByVal w As Variant)
    Dim i As Long
    For i = 0 To UBound(w)
        ws.Columns(i + 1).ColumnWidth = w(i)
    Next i
End Sub

' Absolute reference to one column of the Data sheet, e.g. Data!$H$2:$H$786
Private Function DataCol(ByVal letter As String, ByVal lastRow As Long) As String
    DataCol = "Data!$" & letter & "$2:$" & letter & "$" & lastRow
End Function

' Maps a header cell to a standard column (0 = not used). Add aliases here for new layouts.
Private Function StdColumn(ByVal h As String) As Long
    Select Case LCase$(Trim$(Replace(h, Chr(160), " ")))
        Case "date", "invoice date", "inv date", "txn date": StdColumn = 1
        Case "invoice", "inv no", "invoice no", "invoice number", "inv #": StdColumn = 2
        Case "customer", "account", "customer name": StdColumn = 3
        Case "product group", "category", "group": StdColumn = 4
        Case "qty", "units", "quantity": StdColumn = 5
        Case "net sales", "sales (ex tax)", "net", "sales": StdColumn = 6
        Case "cost", "cogs", "cost of sales": StdColumn = 7
        Case Else: StdColumn = 0
    End Select
End Function

Private Function AllMapped(colOf() As Long) As Boolean
    Dim k As Long
    For k = 1 To N_STD
        If colOf(k) = 0 Then Exit Function
    Next k
    AllMapped = True
End Function

Private Function IsTotalRow(v As Variant, ByVal r As Long) As Boolean
    Dim c As Long
    For c = 1 To Application.Min(3, UBound(v, 2))
        If LCase$(Trim$(CStr(v(r, c)))) Like "*total*" Then IsTotalRow = True: Exit Function
    Next c
End Function

' Dates arrive as real dates or as text such as 2026-07-03 or 03/07/2026.
Private Function ToDate(ByVal x As Variant) As Variant
    Dim s As String
    If IsEmpty(x) Then ToDate = Empty: Exit Function
    If VarType(x) = vbDate Then ToDate = x: Exit Function
    If IsNumeric(x) Then ToDate = CDate(x): Exit Function
    s = Trim$(CStr(x))
    If s Like "####-##-##*" Then
        ToDate = DateSerial(CLng(Left$(s, 4)), CLng(Mid$(s, 6, 2)), CLng(Mid$(s, 9, 2)))
    ElseIf s Like "##/##/####*" Then
        ToDate = DateSerial(CLng(Mid$(s, 7, 4)), CLng(Mid$(s, 4, 2)), CLng(Left$(s, 2)))
    Else
        ToDate = s
    End If
End Function

' Branch_<Branch>_<YYYY-MM>[ anything].xlsx  ->  branch, "YYYY-MM", first day of month
Private Function ParseFileName(ByVal f As String, ByRef branch As String, ByRef monthKey As String, ByRef monthStart As Date) As Boolean
    Dim parts() As String, m As String
    parts = Split(f, "_")
    If UBound(parts) < 2 Then Exit Function
    m = Left$(parts(2), 7)
    If Not (m Like "####-##") Then Exit Function
    branch = parts(1): monthKey = m
    monthStart = DateSerial(CLng(Left$(m, 4)), CLng(Right$(m, 2)), 1)
    ParseFileName = True
End Function

Private Function FirstFileFor(ByVal wsL As Object, ByVal branch As String, ByVal monthKey As String) As String
    Dim r As Long
    r = 2
    Do While Trim$(CStr(wsL.Cells(r, 1).Value)) <> ""
        If wsL.Cells(r, 2).Value = branch And Left$(wsL.Cells(r, 7).Value, 8) = "Imported" Then
            If Format$(CDate(wsL.Cells(r, 3).Value), "yyyy-mm") = monthKey Then FirstFileFor = wsL.Cells(r, 1).Value: Exit Function
        End If
        r = r + 1
    Loop
End Function

Private Function SliceRows(a() As Variant, ByVal n As Long) As Variant
    Dim b() As Variant, r As Long, c As Long
    ReDim b(1 To n, 1 To UBound(a, 2))
    For r = 1 To n
        For c = 1 To UBound(a, 2): b(r, c) = a(r, c): Next c
    Next r
    SliceRows = b
End Function

' Exact names (Branch_Geelong_2026-09.xlsx) sort before copies such as "... (1).xlsx", so that
' if a month arrives twice the original file is imported and the copy is logged as a duplicate.
Private Sub SortFiles(a() As String)
    Dim i As Long, j As Long, t As String
    For i = LBound(a) To UBound(a) - 1
        For j = i + 1 To UBound(a)
            If FileSortKey(a(j)) < FileSortKey(a(i)) Then t = a(i): a(i) = a(j): a(j) = t
        Next j
    Next i
End Sub

Private Function FileSortKey(ByVal f As String) As String
    If LCase$(f) Like "branch_*_####-##.xlsx" Then FileSortKey = "0" & LCase$(f) Else FileSortKey = "1" & LCase$(f)
End Function

Private Sub SortStrings(a() As String)
    Dim i As Long, j As Long, t As String
    For i = LBound(a) To UBound(a) - 1
        For j = i + 1 To UBound(a)
            If LCase$(a(j)) < LCase$(a(i)) Then t = a(i): a(i) = a(j): a(j) = t
        Next j
    Next i
End Sub

Private Sub UniqueStrings(ByVal ws As Object, ByVal col As Long, ByVal lastRow As Long, out() As String, n As Long)
    Dim v As Variant, r As Long, k As Long, found As Boolean
    n = 0
    v = ws.Range(ws.Cells(2, col), ws.Cells(lastRow, col)).Value
    ReDim out(1 To UBound(v, 1))
    For r = 1 To UBound(v, 1)
        found = False
        For k = 1 To n
            If out(k) = CStr(v(r, 1)) Then found = True: Exit For
        Next k
        If Not found Then n = n + 1: out(n) = CStr(v(r, 1))
    Next r
    ReDim Preserve out(1 To n)
    SortStrings out
End Sub

Private Sub UniqueMonths(ByVal ws As Object, ByVal lastRow As Long, out() As Date, n As Long)
    Dim v As Variant, r As Long, k As Long, i As Long, j As Long, t As Date, found As Boolean
    n = 0
    v = ws.Range(ws.Cells(2, 2), ws.Cells(lastRow, 2)).Value
    ReDim out(1 To UBound(v, 1))
    For r = 1 To UBound(v, 1)
        found = False
        For k = 1 To n
            If out(k) = CDate(v(r, 1)) Then found = True: Exit For
        Next k
        If Not found Then n = n + 1: out(n) = CDate(v(r, 1))
    Next r
    ReDim Preserve out(1 To n)
    For i = 1 To n - 1
        For j = i + 1 To n
            If out(j) < out(i) Then t = out(i): out(i) = out(j): out(j) = t
        Next j
    Next i
End Sub