Automatic audit mapping for product certification
Python system that automatically distributes hundreds of audits across sectorial Excel sheets (ISO 9001, 14001, CE marking, product certification...) by mapping families and tags.
Step 01
Problem
Managing audit qualifications for product certification meant manually reviewing each code, determining its family (CE, product, ISO 9001, ISO 14001, control body...) and placing it in the correct Excel sheet. With hundreds of audits, the process was tedious and error-prone.
Step 02
Context and constraints
At AW Certificación, audits are classified by codes with SEA tags (Specific Accreditation Sectors). Each audit could belong to multiple families simultaneously (e.g. CE Marking + ISO 9001), and each family has its own sheet in the qualifications Excel template.
Role: Full system design and development, including duplicate detection, cross-validation, and automatic row generation for uncovered families.
Step 03
Key decisions
- Use SEA tags as the single classification system: each audit carries tags that automatically determine its location across all applicable sheets.
- Multi-sheet mapping: the same audit can appear in multiple sheets (CE + product + sectorial) without duplicating manual effort.
- Smart family detection: the system recognizes 33 CE families, 15+ product families, and 5+ control body families.
- Automatic row generation when a family does not exist in the template.
- Cross-validation: verifies all audit codes appear in the output and highlights missing ones in yellow.
Step 04
Outcomes
- Qualification process that used to take hours of manual review now runs in seconds.
- Complete coverage of CE families (33), product families (15+), sectorial, and control body categories.
- Reduced errors from omission or incorrect placement of audits.
- Built-in validation that alerts of inconsistencies before delivery.
Metrics
- Hundreds of audits processed automatically per run.
- 33 recognized CE families: CEAC, CEAR, CEAT, CEBE, CEBG, CEBL, CECD, CECH, CECL, CECT, CECV, CEEB, CEER, CEES, CEFH, CEGT, CELB, CELC, CELF, CEMA, CEMB, CEME, CEMR, CEPA, CEPB, CEPC, CEPD, CEPE, CEPG, CEPH, CEPN, CESC, CEVP.
- Automatic validation with anomaly highlighting.
Step 05
Learnings and next improvement
How it works
The system reads a source Excel with the audit list, extracts codes and their SEA tags, and distributes each audit across the corresponding sheets in the qualifications Excel template.
Process flow
Source Excel → [Audit reading] → [Tag extraction] → [Multi-sheet mapping] → Qualified Excel
│
┌────────────────────┼────────────────────┐
▼ ▼ ▼
ISO 9001 CE Product
(Sectorial) (CE Marking) (Certification)Tag system (SEAs)
Each audit carries tags that determine its classification:
- SCxx → ISO 9001 sectorial (e.g. SC12, SC28A)
- MAxx → ISO 14001 sectorial (e.g. MA23, MA24)
- CEXX → CE Marking (33 families: CEAR, CECT, CEMB…)
- PRHP, CVBL, CVBA… → Product certification
- OCHP, ET04, ITC08… → Control body
- EHC, EHA, VSG, GTC… → Laboratory and ECCE
Supported CE families (33)
| Code | Description |
|---|---|
| CEAC | Fired clay pieces |
| CEAR | Aggregates |
| CEAT | Admixtures |
| CEBE | Bitumens and binders |
| CEBG | Geosynthetic barriers |
| CEBL | Concrete blocks |
| CECD | Cement distribution centers |
| CECH | Smoke ducts |
| CECL | Limes |
| CECT | Cements |
| CECV | Fly ash |
| CEEB | Bituminous emulsions |
| CEER | Concrete structure protection |
| CEES | Slags |
| CEFH | Fibers for concrete |
| CEGT | Geotextiles |
| CELB | Bituminous grouts |
| CELC | Hot-rolled steel |
| CELF | Flexible waterproofing sheets |
| CEMA | Welded wire mesh |
| CEMB | Bituminous mixtures |
| CEME | Steel structures |
| CEMR | Masonry mortars |
| CEPA | Artificial stone |
| CEPB-E | Pyrotechnic articles (modules B/C/D/E) |
| CEPG | Pigments |
| CEPH | Precast concrete |
| CEPN | Natural stone |
| CESC | Vehicle restraint systems |
| CEVP | Windows and doors |
Cross-validation
After mapping, the system compares the original audit list against the generated Excel. If any audit is missing from the output, the corresponding cell in the source Excel is highlighted in yellow, allowing the operator to identify and fix the error before delivery.
Learnings
- A well-designed tagging system dramatically simplifies the classification of complex data with multiple overlapping categories.
- Cross-validation is not optional: in certification processes, a missing audit can delay an accreditation.
- System maintainability is key: adding a new family only requires adding its code to the classification dictionary.
Next improvement
Integrate the system with a simple web interface that allows uploading the Excel, running the mapping, and downloading the result without requiring Python installed.
Step 06
Visual resources
