Data Validation Rule Engine (Excel VBA)
5,000 records in, 3,851 accepted, 1,263 violations caught, 5,256 cells repaired.
What does the work involve?
Portfolio project. All data is fictional and simulated.
The problem always has the same shape: a batch of transaction data arrives, and a regulation decides which fields are mandatory, which are optional, and what valid means for each one. Five thousand records. Manual checking does not scale, and it never leaves a trail.
So I made the regulation executable.
The rule sheet
18 rules over 14 fields: patterns, lengths, reference lookups, a map of products to categories, numeric ranges, date windows and duplicate detection. Every field is flagged mandatory or optional. Every rule carries a severity, blocking or warning, and a switch that fixes issues automatically. Changing the regulation is an edit of a single row in Excel, not a code change.
The engine
One button runs the batch in seconds. It cleans first and judges second: name casing, stray spaces, phone separators, canonical reference values, category rebuilt from the product map, dates normalised, every change written to a fix audit column. Then the rules run, every rejected cell is marked, and each row gets a verdict: valid, warning, or invalid.
What came out
5,000 records in, 3,851 accepted, 413 accepted with warnings, 736 rejected with a precise reason. 1,263 violations caught before they reached reporting. 5,256 cells repaired automatically. Compliance on the first pass: 77.02%.
The deliverables
An error report with one row per violation. Valid and invalid CSV splits for downstream systems. An audit log that only ever grows, with run id, user, counts and duration. And a separate dashboard workbook, 7 KPI cards, 6 native Excel charts, that rebuilds from the engine's statistics export with one click.
Pure Excel VBA. No add-ins. Fully reproducible.
What did it come to?
77.02%first-pass compliance