Case Study 02 • High-Volume Reporting

600K-LineReporting System

A monthly Excel/VBA workflow designed around the point where familiar workbook techniques—raw tabs, formulas, filters, and repeated formatting—became unstable.

  • Rows600K+
  • Columns51
  • Data points30M+ monthly

Overview

Excel was still the right delivery environment—but the workbook model had to change

A New York City telecom portfolio grew beyond 600,000 lines of service. Its monthly output contained 51 columns assembled from 30+ source files, making the final report useful as a primary dataset for downstream analytics, controlled loading, and invoice work.

The challenge was not that Excel could never hold the result. The challenge was that the existing dynamic templates performed too many expensive workbook operations while building it.

Problem

Normal workbook behavior multiplied at portfolio scale

Large raw-data tabs, AutoFilters, formatting, and formula-based transformations caused freezes and crashes. In observed problem runs, a formula-driven version exceeded eight hours without completing correctly, while correction cycles in Power Query took roughly 20–30 minutes per refresh. A different architecture was needed inside the tools available.

Excel delivery

The final dataset still needed to be usable in the established Excel-based workflow.

Unusual scale

The portfolio exceeded 600,000 service-level rows and outgrew the existing workbook pattern.

Many sources

Usage, billing, equipment, order, identifier, and other recurring files had to be recognized and combined.

Format variation

Source systems did not always represent equivalent data with identical headers or layouts.

Approach

Keep the heavy work out of the workbook grid

The revised process staged monthly inputs in a folder, opened one source at a time, identified its role from the headers, and used arrays and dictionaries to assemble the report. The workbook received a static result instead of carrying the formulas, raw tabs, and intermediate state used to produce it.

  1. 01

    Stage sources

    Place the recurring monthly files in a controlled folder rather than opening the full source set together.

  2. 02

    Identify data type

    Read header patterns to distinguish usage, billing, equipment, order, identifier, and related inputs.

  3. 03

    Build in memory

    Use arrays for bulk values and dictionaries for keyed lookups, minimizing worksheet interaction.

  4. 04

    Write and review

    Output static values to the final structure, then make targeted manual corrections for known exceptions.

Sanitized Evidence

A 51-field output organized by purpose, not worksheet position

Illustrative schema groups

The final columns are grouped into identity, account, service, usage, billing, and control fields. The group counts below total 51; labels are generalized.

Conceptual grouping for portfolio explanation. No real column names, values, or source-system identifiers are shown.

Key Decisions

Performance choices tied to actual bottlenecks

Prefer static output over a dynamic template

Reasoning

The report needed to be reviewable and deliverable; it did not need millions of live formula relationships after generation.

Tradeoff

Changes required another controlled run rather than automatic cell-by-cell recalculation.

Process one source file at a time

Reasoning

Limiting open inputs reduced memory pressure and avoided carrying unnecessary raw worksheets throughout the run.

Tradeoff

Folder discipline and reliable source identification became essential.

Use headers as the interface

Reasoning

Header detection made the workflow less dependent on file order and supported multiple recurring data types.

Tradeoff

Material header changes still required analysis and code updates rather than being silently accepted.

Tools & Validation

Purpose-built VBA patterns

  • Excel VBA
  • Arrays
  • Dictionaries
  • Header detection
  • Folder staging
  • Static output

Validation approach

  • Expected source types were present before completion
  • Output row and column structure matched the reporting specification
  • Known source-format differences were normalized intentionally
  • Final values remained available for targeted exception review

Result & Impact

A monthly dataset that could be completed and used

600K+subscriber-level rows
51final report columns
30M+data points per run
30+monthly source files

The process made the recurring report completable after the existing dynamic approach became unstable. The output served downstream analytics and reporting work while retaining a practical Excel review experience.

Learnings

Optimize the architecture before optimizing individual lines

  • The biggest performance gain came from changing where work happened, not from making the same worksheet operations slightly faster.
  • Static output is often a feature when the deliverable needs stability, traceability, and review.
  • Arrays and dictionaries are most useful when paired with disciplined source identification and clear exception handling.
  • Tool selection should reflect the correction cycle and operating environment, not only the initial run.

Additional context

This solution was designed for a specific reporting environment in which Excel was both an available automation surface and the expected delivery format. It demonstrates practical large-file processing in VBA; it does not claim that VBA is the universal choice for datasets of this size.