A purchasing file can look tidy while still containing an incorrect vendor match or a missing order line. A useful cleanup should let someone trace each result back to its source and understand which questions remain open. Start with a working copy, clear rules, and a check that every row and amount is accounted for.
1. Preserve the source and define a row
Keep the original export unchanged. Record its date, filename, and row count, then give each source row a stable reference. Before removing duplicates, establish what a row represents: an order line, shipment, receipt, or invoice line. Two rows with the same purchase order and item may represent separate deliveries.
Confirm which reference files to use for vendor IDs, item codes, and other fields that must match an approved list. Do not assume the newest-looking spreadsheet is the right one.
2. Write the rules before applying them
Separate formatting changes from business decisions. Trimming spaces may be straightforward; deciding that two vendor names represent one company needs evidence. Preserve meaningful leading zeros, punctuation, and case in identifiers. Confirm date formats, currency, and units before converting values.
Keep a rule log with the affected field, transformation, and reference used. If a rule could merge distinct records, test it on examples and get the process owner’s approval first.
3. Keep uncertain records visible
In the real procurement example, currency text is converted to numeric values without changing the pound amount. An extra space is removed from a source column heading so matching fields appear in one column. Possible duplicate transactions are kept for review.
Use an exception log with the source row, issue, evidence needed, decision owner, and status. A matching name or transaction reference is a reason to check the source, not proof that two rows are the same transaction.
4. Check the finished file against the source
The procurement handoff preserves all 7,787 selected spending lines. The original, prepared, and supplier-report totals are reconciled in the downloadable workbook, and no source line is removed.
Those checks explain where the source values went. They do not prove that every underlying transaction is correct. Keep calculated amounts separate, reconcile each currency separately, and record the checks required before any live use.
Before you use the cleaned file
- Can every output row be traced to its source?
- Do row counts and comparable totals reconcile?
- Are excluded duplicates retained and explained?
- Do unresolved records have a decision owner?
- Are formulas, dates, identifiers, and representative edge cases checked?
Common failures include deleting apparent duplicates too early, treating blanks as zero, and hiding unresolved records to make the totals look cleaner. A clear handoff includes the original data, cleaned view, rules, and exceptions.
Start with one manageable file
The fixed spreadsheet cleanup packages each cover one flat CSV file or one XLSX file with one worksheet, containing one rectangular table with one header row and an input file up to 12 MB. The $49 USD simple cleanup covers up to 20,000 data rows, 20 columns, three agreed repeatable cleanup rules, and one consolidated in-scope preference revision. The $99 USD standard cleanup covers up to 50,000 data rows, 25 columns, five rules, two consolidated in-scope preference revisions, before-and-after sums for up to three agreed numeric columns, and a rule-by-rule validation report. A free fit review checks the file structure, requested rules, and how many records may need your decision before the accepted scope and delivery date are confirmed in writing.
Use these checks with your own records, systems, and approved policies. Linked portfolio examples identify their public sources and limits.
Clear scope. Careful checks.