How to Clean a Vendor Master File Before an ERP Migration

ERP migrations fail for a lot of reasons. Scope creep, change management, integration complexity. But one of the most common and most preventable causes is dirty data that gets migrated straight into the new system.

Your vendor master file is usually the worst offender.

By the time a company has been operating for three or more years, the vendor master typically contains duplicate entries for the same supplier (filed under different legal names, different abbreviations, or different remit-to addresses), stale records for vendors no longer in use, missing tax IDs, and payment terms that were set up years ago and never reviewed.

Migrating all of that into a new system doesn't fix it. It just makes it the new system's problem, and your problem, on day one of go-live.

Why Vendor Masters Get Messy

A supplier gets added when someone needs to create a PO quickly and doesn't check whether the vendor already exists. The same supplier is added again six months later under a slightly different name. A vendor changes their legal name after an acquisition and the old record stays active. Multiple remit-to addresses get created as separate vendor records when they should be sub-records of the same parent.

Audits regularly find 15 to 30 percent duplication rates in systems that haven't been cleaned in several years. Duplicate vendor records create duplicate payment risk, complicate 1099 and tax reporting, and generate audit findings that require remediation at the worst possible time.

What to Fix Before Migration

Category 1: Duplicate vendor records. Same supplier under multiple records. Common patterns: "Staples Inc." and "Staples, Inc." and "STAPLES" as three separate vendors; old record from before a corporate name change still active alongside the new record.

Category 2: Missing required fields. Most ERPs require legal name, address, payment terms, tax ID or DUNS, currency, and banking details. Records missing required fields will either fail import validation or create problems in payment runs.

Category 3: Stale and inactive vendors. Suppliers you haven't transacted with in two or more years. Review these and either inactivate or delete them before migration.

Category 4: Formatting inconsistencies. Address formats that don't match USPS standards, phone numbers in multiple formats, state codes spelled out in some records and abbreviated in others.

Step-by-Step Vendor Master Cleanup

Step 1: Export everything. Pull the full vendor master export. Include vendor ID, legal name, short name, all address fields, tax ID, DUNS, payment terms, currency, banking details, active/inactive status, and last transaction date. The last transaction date is critical for identifying stale records.

Step 2: Flag stale records first. Sort by last transaction date. Any vendor with no transactions in the past 24 months is a candidate for inactivation. Pull these into a separate review list and work through them with the AP and procurement teams before the migration date.

Step 3: Identify duplicates. Sort remaining records by vendor name. Obvious duplicates cluster together. For less obvious ones, same DUNS number across different names, same bank account number across different records, you need cross-field matching.

Run a DUNS number match: any two records with the same DUNS are the same legal entity. If your records have DUNS numbers populated, this is the fastest path to clean deduplication.

For records without DUNS, apply fuzzy matching on the legal name field. Patterns to catch: punctuation differences, abbreviation vs. spelled-out, suffix variations (Inc. vs. Incorporated vs. Corp.).

Step 4: Resolve each duplicate. For each duplicate pair, decide which record is the master. The master should be the one with the most complete data, the most recent activity, and confirmed current payment terms and banking details. Document every merge decision.

Step 5: Fill missing required fields. Tax ID (verify against the vendor's W-9), DUNS number (look up free on Dun & Bradstreet), payment terms (confirm with current contracts), primary address (validate against USPS or Google Places). For address enrichment at scale, a tool that pulls verified addresses from Google Places given a business name and partial location data saves significant manual verification time.

Step 6: Validate against your new ERP's import template. Every ERP has specific import requirements. Map your cleaned data to the import template before the migration date. Validate in a sandbox environment first.

The Cost of Not Cleaning Before Migration

Cleaning dirty data after go-live is significantly more expensive than cleaning it before. In the new system, every fix requires a change request, a review process, and often a staging environment test. What takes 20 minutes in a spreadsheet before migration can take four hours of IT involvement after.

The other cost is payment risk. Duplicate vendor records that migrate into the new system can generate duplicate payment runs before anyone catches the problem.

FAQ

How do I find duplicate vendors in SAP? For full deduplication analysis, export to Excel or a cleaning tool and work there. It's faster and more flexible than working directly in SAP.

What should I do with vendors who have multiple remit-to addresses? These should generally be one vendor record with multiple sub-records or alternative payee records, depending on your ERP's data model. Consult your implementation partner.

How far back should I go to define "inactive"? 24 months is the most common threshold. Some organizations use 36 months for capital equipment vendors who may have legitimate multi-year gaps between purchases.

Is it better to clean vendor master data inside the ERP or in a spreadsheet? For bulk changes, spreadsheet cleaning followed by re-import is usually faster. Direct ERP editing is better for targeted one-off changes.