Two-Year TB Comparator: Spot New, Closed, and Changed Accounts Instantly

TL;DR: Paste PY trial balance into one sheet, CY into another. Run the macro. You get a new sheet showing every account side-by-side — dollar variance, percentage change, and whether it’s new, closed, or materially different. One click replaces 20 minutes of manual VLOOKUP.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• Two trial balances (prior year and current year) with account codes in column A and descriptions in column B
• Balances must be numeric — text-formatted numbers (e.g., "50,000") will be skipped
• The macro expects data starting at row 2 (row 1 is headers) — adjust HEADER_ROW if needed

Limitations:

• Duplicate account codes in the same TB will produce a warning — the macro takes the first occurrence
• Account codes that differ only by leading zeros ("11010" vs 11010) are normalized and will match
• The macro does not modify your original TB sheets — all output goes to a new sheet
• Protected workbook structure will prevent sheet creation — unprotect first

#The Macro

Option Explicit

Sub CompareTrialBalances()
    ' ── Two-Year TB Comparator ─────────────────────────
    ' Compares two trial balances (PY and CY) by account
    ' code and produces a formatted comparison sheet with
    ' dollar variance, percentage variance, and status.
    '
    ' Setup: Paste PY TB into "TB-PY" sheet, CY TB into
    '        "TB-CY" sheet. Account codes in col A,
    '        descriptions in col B, balances in col C.
    ' ────────────────────────────────────────────────────

    ' ── Configuration ──────────────────────────────────
    Const PY_SHEET As String = "TB-PY"
    Const CY_SHEET As String = "TB-CY"
    Const OUT_SHEET As String = "TB-Compare"

    Const ACCT_CODE_COL As Long = 1   ' A = account codes
    Const DESC_COL As Long = 2        ' B = descriptions
    Const BALANCE_COL As Long = 3     ' C = balances
    Const HEADER_ROW As Long = 1      ' Row 1 = headers

    Const MATERIALITY As Double = 0   ' Set > 0 to ignore small variances

    ' ── State management ───────────────────────────────
    Dim prevCalc As XlCalculation
    prevCalc = Application.Calculation
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

    ' ── Variables ──────────────────────────────────────
    Dim dict As Collection
    Dim wsPY As Worksheet, wsCY As Worksheet, wsOut As Worksheet
    Dim pyLastRow As Long, cyLastRow As Long
    Dim outRow As Long, i As Long
    Dim acctCode As String, pyBal As Double, cyBal As Double
    Dim variance As Double, pctVar As Variant
    Dim pyDesc As String, cyDesc As String
    Dim matchCount As Long, newCount As Long, closedCount As Long
    Dim materialCount As Long

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    ' ── Step 1: Validate input sheets ──────────────────
    On Error Resume Next
    Set wsPY = ThisWorkbook.Worksheets(PY_SHEET)
    Set wsCY = ThisWorkbook.Worksheets(CY_SHEET)
    On Error GoTo CleanUp

    If wsPY Is Nothing Then
        MsgBox "Sheet '" & PY_SHEET & "' not found. " & vbCrLf & _
               "Create a sheet named '" & PY_SHEET & "' and " & _
               "paste your prior-year TB there.", vbExclamation, "Missing Sheet"
        GoTo CleanUp
    End If
    If wsCY Is Nothing Then
        MsgBox "Sheet '" & CY_SHEET & "' not found. " & vbCrLf & _
               "Create a sheet named '" & CY_SHEET & "' and " & _
               "paste your current-year TB there.", vbExclamation, "Missing Sheet"
        GoTo CleanUp
    End If

    ' ── Step 2: Remove old output sheet ────────────────
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets(OUT_SHEET).Delete
    Application.DisplayAlerts = True
    On Error GoTo CleanUp

    ' ── Step 3: Build lookup table from Prior Year ─────
    ' Uses a VBA Collection (built-in, no ActiveX required).
    ' Key = normalized account code, Value = row number.
    Set dict = New Collection
    pyLastRow = wsPY.Cells(wsPY.Rows.Count, ACCT_CODE_COL).End(xlUp).Row
    If pyLastRow <= HEADER_ROW Then
        MsgBox "No data found in '" & PY_SHEET & "'.", vbExclamation
        GoTo CleanUp
    End If

    For i = HEADER_ROW + 1 To pyLastRow
        acctCode = NormalizeAccountCode(wsPY.Cells(i, ACCT_CODE_COL).Value)
        If acctCode <> "" Then
            If CollectionContains(dict, acctCode) Then
                MsgBox "Duplicate account code in " & PY_SHEET & _
                       ": " & acctCode & vbCrLf & _
                       "(Row " & i & "). Using first occurrence.", _
                       vbExclamation, "Duplicate Account"
            Else
                dict.Add i, acctCode
            End If
        End If
    Next i

    If dict.Count = 0 Then
        MsgBox "No account codes found in '" & PY_SHEET & "'.", vbExclamation
        GoTo CleanUp
    End If

    ' ── Step 4: Create output sheet ────────────────────
    Set wsOut = ThisWorkbook.Worksheets.Add( _
        Before:=ThisWorkbook.Worksheets(1))
    wsOut.Name = OUT_SHEET

    ' ── Step 5: Write headers ──────────────────────────
    wsOut.Range("A1:G1").Value = Array( _
        "Account Code", "Description", _
        "PY Balance", "CY Balance", _
        "Variance ($)", "Variance (%)", "Status")
    wsOut.Range("A1:G1").Font.Bold = True
    wsOut.Range("A1:G1").Interior.Color = RGB(50, 50, 50)
    wsOut.Range("A1:G1").Font.Color = vbWhite

    ' ── Step 6: Build output from Current Year ─────────
    cyLastRow = wsCY.Cells(wsCY.Rows.Count, ACCT_CODE_COL).End(xlUp).Row
    If cyLastRow <= HEADER_ROW Then
        MsgBox "No data found in '" & CY_SHEET & "'.", vbExclamation
        GoTo CleanUp
    End If

    outRow = 2
    matchCount = 0
    newCount = 0
    closedCount = 0
    materialCount = 0

    Dim pyRow As Variant
    Dim status As String
    Dim processed As New Collection

    For i = HEADER_ROW + 1 To cyLastRow
        acctCode = NormalizeAccountCode(wsCY.Cells(i, ACCT_CODE_COL).Value)
        If acctCode = "" Then GoTo NextCYRow

        cyBal = SafeNumeric(wsCY.Cells(i, BALANCE_COL).Value)
        cyDesc = CStr(wsCY.Cells(i, DESC_COL).Value & "")
        processed.Add True, acctCode

        If CollectionContains(dict, acctCode) Then
            ' ── Matched account ────────────────────────
            pyRow = dict(acctCode)
            pyBal = SafeNumeric(wsPY.Cells(CLng(pyRow), BALANCE_COL).Value)
            pyDesc = CStr(wsPY.Cells(CLng(pyRow), DESC_COL).Value & "")
            variance = cyBal - pyBal

            ' Percentage variance (guard against divide-by-zero)
            If Abs(pyBal) > 0.001 Then
                pctVar = variance / Abs(pyBal)
            ElseIf Abs(cyBal) > 0.001 Then
                pctVar = 1  ' PY was zero, CY has a balance → 100% change
            Else
                pctVar = 0
            End If

            ' Determine status
            If Abs(variance) <= MATERIALITY Then
                status = "MATCH"
            Else
                status = "CHANGED"
                materialCount = materialCount + 1
            End If

            matchCount = matchCount + 1
            ' Don't remove from dict — we'll use the processed
            ' collection to identify closed accounts later

        Else
            ' ── New account (CY only) ──────────────────
            pyBal = 0
            pyDesc = "—"
            variance = cyBal
            pctVar = Empty
            status = "NEW"
            newCount = newCount + 1
        End If

        ' Write row
        wsOut.Cells(outRow, ACCT_CODE_COL).Value = "'" & acctCode
        wsOut.Cells(outRow, DESC_COL).Value = cyDesc
        wsOut.Cells(outRow, 3).Value = pyBal
        wsOut.Cells(outRow, 4).Value = cyBal
        wsOut.Cells(outRow, 5).Value = variance
        If Not IsEmpty(pctVar) Then
            wsOut.Cells(outRow, 6).Value = pctVar
        End If
        wsOut.Cells(outRow, 7).Value = status

        ApplyRowColor wsOut, outRow, status
        outRow = outRow + 1

NextCYRow:
    Next i

    ' ── Step 7: Remaining PY accounts (closed) ─────────
    ' An account is "closed" if it's in the PY sheet but
    ' was never seen in the CY pass (not in 'processed').
    For i = HEADER_ROW + 1 To pyLastRow
        acctCode = NormalizeAccountCode(wsPY.Cells(i, ACCT_CODE_COL).Value)
        If acctCode = "" Then GoTo NextPYRow
        If CollectionContains(processed, acctCode) Then GoTo NextPYRow

        pyBal = SafeNumeric(wsPY.Cells(i, BALANCE_COL).Value)
        pyDesc = CStr(wsPY.Cells(i, DESC_COL).Value & "")

        wsOut.Cells(outRow, ACCT_CODE_COL).Value = "'" & acctCode
        wsOut.Cells(outRow, DESC_COL).Value = pyDesc
        wsOut.Cells(outRow, 3).Value = pyBal
        wsOut.Cells(outRow, 4).Value = 0
        wsOut.Cells(outRow, 5).Value = -pyBal
        wsOut.Cells(outRow, 7).Value = "CLOSED"

        ApplyRowColor wsOut, outRow, "CLOSED"
        outRow = outRow + 1
        closedCount = closedCount + 1

NextPYRow:
    Next i

    ' ── Step 8: Format output ──────────────────────────
    With wsOut
        .Columns("A:G").AutoFit
        .Columns("C:E").NumberFormat = "#,##0.00"
        .Columns("F").NumberFormat = "0.0%"
        .Range("A1").Select
        ActiveWindow.FreezePanes = True
    End With

    ' Sort by account code (numeric-then-alpha)
    If outRow > 3 Then
        wsOut.Sort.SortFields.Clear
        wsOut.Sort.SortFields.Add _
            Key:=wsOut.Range("A2:A" & outRow - 1), _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending
        With wsOut.Sort
            .SetRange wsOut.Range("A1:G" & outRow - 1)
            .Header = xlYes
            .Apply
        End With
    End If

    ' ── Step 9: Summary ────────────────────────────────
    Dim msg As String
    msg = "Comparison complete." & vbCrLf & vbCrLf & _
          "  Matched:   " & matchCount & vbCrLf & _
          "  New:       " & newCount & vbCrLf & _
          "  Closed:    " & closedCount & vbCrLf
    If materialCount > 0 Then
        msg = msg & "  Changed > materiality: " & materialCount & vbCrLf
    End If
    msg = msg & vbCrLf & "See '" & OUT_SHEET & "' sheet."

    MsgBox msg, vbInformation, "TB Comparison"

CleanUp:
    ' ── Restore Excel state ────────────────────────────
    Application.ScreenUpdating = True
    Application.Calculation = prevCalc
    Application.EnableEvents = True

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

' ── Helper: Check if a Collection contains a key ──────
Private Function CollectionContains( _
    ByRef col As Collection, _
    ByVal key As String) As Boolean

    On Error Resume Next
    Dim dummy As Variant
    dummy = col(key)
    CollectionContains = (Err.Number = 0)
    On Error GoTo 0
End Function

' ── Helper: Remove an item from a Collection by key ───
Private Sub CollectionRemove( _
    ByRef col As Collection, _
    ByVal key As String)

    On Error Resume Next
    col.Remove key
    On Error GoTo 0
End Sub

' ── Helper: Normalize account code for matching ────────
Private Function NormalizeAccountCode(val As Variant) As String
    ' Converts to string, trims, strips hyphens and spaces.
    ' "1000" matches "01000" matches "1000-00" matches 1000.
    Dim s As String
    s = Trim(CStr(val))
    s = Replace(Replace(s, "-", ""), " ", "")
    NormalizeAccountCode = s
End Function

' ── Helper: Convert cell value to numeric safely ───────
Private Function SafeNumeric(val As Variant) As Double
    If IsNumeric(val) Then
        SafeNumeric = CDbl(val)
    Else
        SafeNumeric = 0
    End If
End Function

' ── Helper: Color-code a row by status ─────────────────
Private Sub ApplyRowColor( _
    ByRef ws As Worksheet, _
    ByVal r As Long, _
    ByVal status As String)

    Dim color As Long

    Select Case status
        Case "MATCH"
            color = RGB(220, 252, 231)   ' light green
        Case "CHANGED"
            color = RGB(255, 237, 213)   ' light orange
        Case "NEW"
            color = RGB(219, 234, 254)   ' light blue
        Case "CLOSED"
            color = RGB(229, 231, 235)   ' light gray
    End Select

    ws.Range("A" & r & ":G" & r).Interior.Color = color

    ' Bold the status label
    ws.Cells(r, 7).Font.Bold = True
End Sub

#How It Works

#The Collection does the heavy lifting

The macro reads every account code from the prior-year TB into a VBA Collection (built into VBA, no ActiveX or external references required). Each normalized account code is the key, and the row number is the value. Then it walks through the current-year TB: for each account, it checks if the code exists in the collection. Exists? Do the variance math. Doesn’t exist? It’s a new account.

After the CY pass, the macro loops through the PY sheet one more time: any account that wasn’t seen in the CY pass (not in the processed collection) is a closed account.

A Collection lookup iterates internally but is fast enough for TBs with hundreds of accounts. For TBs exceeding 2,000 rows, the performance difference between Collection and Scripting.Dictionary becomes noticeable — see Adapt It for a Dictionary variant.

#Account code normalization — the part that saves you from silent failures

Private Function NormalizeAccountCode(val As Variant) As String
    Dim s As String
    s = Trim(CStr(val))
    s = Replace(Replace(s, "-", ""), " ", "")
    NormalizeAccountCode = s
End Function

Trial balances from different systems rarely agree on formatting. QuickBooks exports 1000 as a number. UltraTax exports "01000" as text. CCH exports "1000-00" with a sub-account suffix. Without normalization, none of these match — the macro would report three separate, unmatched accounts when they’re all the same GL code.

This function strips hyphens, spaces, and whitespace, then compares the cleaned string. "1000" matches 1000 matches "01000" matches "1000-00".

Important: If your firm uses sub-accounts where the suffix matters ("1000-01" ≠ "1000-02"), remove the Replace(s, "-", "") line. Only strip dashes if you know they’re cosmetic.

#Variance tolerance — not every penny needs a flag

Const MATERIALITY As Double = 0

Set this to 0 and every cent of difference gets a “CHANGED” status. Set it to 100 and rounding differences under $100 get “MATCH” instead. Want percentage materiality? You’d add a second check in the Adapt It section.

The percentage variance formula handles the divide-by-zero case explicitly:

If Abs(pyBal) > 0.001 Then
    pctVar = variance / Abs(pyBal)
ElseIf Abs(cyBal) > 0.001 Then
    pctVar = 1    ' PY was $0, CY has a balance → treat as 100% change
Else
    pctVar = 0     ' Both $0 → no change
End If

A preparer who sees “N/A” in the variance column for a line that went from $0 to $50,000 will be annoyed. Showing 100% tells the story: this account had nothing last year and everything this year.

#State management — why ScreenUpdating and Calculation get toggled

This macro writes to hundreds of cells, applies color formatting, then sorts the result. Without ScreenUpdating = False, the user watches every cell populate one at a time (and wonders if Excel froze). Without Calculation = xlManual, every cell write triggers a recalculation cascade across the workbook.

The CleanUp label at the bottom uses On Error GoTo CleanUp, so even if the macro crashes mid-way, Excel’s state gets restored. A crashed macro that leaves Calculation in manual mode is a support ticket waiting to happen.

#Sort by account code, not by status

The output is sorted by account code (numeric-then-alpha). Sorting by status would group all the green rows, then blue, then orange — visually appealing but useless for a preparer who needs to scan accounts in GL order. The status column + color coding provides the visual scan; the account code sort provides the navigability.

#Why color AND a status column

Color alone fails for color-blind reviewers and PDF printouts. Status text alone is slower to scan. Both together means the preparer can skim by color and the reviewer can read the status column. The PDF version of this sheet looks professional and reviewable.

#Why “paste into sheets” instead of range selection

Both existing macros on this blog use the “paste your data into designated sheets” pattern. It’s tested, consistent, and eliminates the most common failure mode of range-selection macros: the user highlights the wrong range, includes a blank row, or selects a non-contiguous block. Name your sheets TB-PY and TB-CY, paste the data, and run. The Adapt It section explains how to change sheet names if your workflow is different.

#Adapt It