Enforcing a data regulation with 18 rules in Excel.
3,851 records accepted, 5,256 cells repaired: 77.02% compliance on the first pass.
What does the work involve?
Portfolio case study. All data is fictional and simulated.
Problem
A data governance regulation classifies every transaction field as mandatory or optional and defines what valid means for each one. The batch is 5,000 records. Manual review is slow, two people reach two different answers, and nothing is auditable.
Approach
I treated the regulation as configuration, not code. A Rules sheet holds 18 rules: patterns, lengths, reference lookups, a product and category map across fields, numeric ranges, date windows and composite duplicate detection, each with a severity, blocking or warning, and a switch that fixes issues automatically.
The VBA engine cleans first and judges second. Deterministic fixes, casing, spaces, phone formats, canonical reference values, dates, are applied and logged cell by cell. Then the rules run, every rejected cell is coloured, and each row is stamped: valid, warning, or invalid.
Result
3,851 records accepted. 413 accepted with warnings. 736 rejected with a documented reason. 1,263 violations caught before reporting: the biggest sources being the approval threshold for high-value transactions, invalid email and phone formats, and cities outside the registered service area. 5,256 cells repaired automatically.
Compliance stopped being an opinion. It became a 77.02% figure with an error report, split exports and an audit log behind it, and a dashboard the management team can read in ten seconds.
What did it come to?
1,263violations caught before reporting