How to Reconcile Two Customer or SKU Exports by ID: A Practical Workflow

1. Prepare Your Exports: Normalization and Header Alignment

Before comparing records, identify the key column in each export and make its representation consistent. Header names do not need to be identical for a simple value-membership comparison, but you must know which column contains the same type of ID on both sides.

Normalize leading and trailing spaces, letter case when appropriate, blank IDs, and data types. Preserve identifiers such as 00123 as text. Extra descriptive columns can remain in the source files, but copy only the two ID columns into a focused column-comparison step.

Do not run a line-based trim command over a quoted CSV file: quoted fields may contain delimiters or line breaks. Use a spreadsheet import, Power Query, or a real CSV parser for structured files.

1. Open both exports in a spreadsheet or text editor.
2. Standardize column headers: same names, same case, no extra spaces.
3. Delete any temporary or irrelevant columns that are not part of the comparison.
4. Ensure the ID column (e.g., CustomerID, SKU) is formatted as text to avoid numeric rounding issues.
5. Clean data using =TRIM() or a list cleaner to remove extra whitespace.

=TRIM(A2)

2. Identify and Handle Duplicate IDs

Duplicate IDs within either export can make a one-to-one reconciliation ambiguous. A lookup may report that an ID exists while hiding the fact that it appears twice on one side and once on the other.

Audit duplicate IDs before comparison. Do not automatically delete entire records merely because the ID repeats: multiple rows may be legitimate, such as several orders for one customer. Resolve the business rule, add a sub-identifier when necessary, and record any consolidation decision.

1. Count total rows in each file.
2. Select the ID column and run a duplicate check (e.g., =COUNTIF(range, B2)>1).
3. Record the duplicate count and decide whether to keep the first occurrence or flag for manual review.
4. Remove or consolidate duplicates to create a clean unique ID list for each export.

• Tip: In Excel, pivot tables can quickly list duplicate IDs along with their counts.
• Caution: If your file has legitimate duplicate IDs with different data (e.g., multiple orders for same customer), you must treat them as separate records; consider adding a unique sub-identifier.

SELECT CustomerID, COUNT(*) FROM Source GROUP BY CustomerID HAVING COUNT(*) > 1;

=IF(COUNTIF($B$2:$B$1000, B2)>1, "Duplicate", "Unique")

3. Compare ID Membership in Both Directions

With the ID columns normalized, identify keys that exist only in Source and keys that exist only in Target. This is a two-direction membership comparison, similar to the anti-join portions of a full outer join.

In Excel, use XLOOKUP, VLOOKUP, MATCH, or Power Query. In CompareTwoLists, paste the two extracted ID columns into Compare Two Columns and review matches, first-only values, second-only values, or the union. The tool compares values; it does not merge complete records or map headers.

After finding missing IDs, return to the original exports to retrieve and review the corresponding full records.

1. Create a new column in Source called 'In_Target' and use VLOOKUP to check if each ID exists in Target.
2. Similarly, check Target IDs against Source.
3. Filter each list for missing IDs and export as separate missing records reports.
4. Review the missing records: are they legitimate differences or anomalies? Document findings.

=IF(ISNA(VLOOKUP(A2, Target!$A$2:$A$5000, 1, FALSE)), "Missing in Target", "Present")

SELECT s.* FROM Source s LEFT JOIN Target t ON s.CustomerID = t.CustomerID WHERE t.CustomerID IS NULL;

SELECT t.* FROM Target t LEFT JOIN Source s ON t.CustomerID = s.CustomerID WHERE s.CustomerID IS NULL;

4. Detect Changed Records

After identifying IDs present in both exports, compare the fields that matter for each matched record, such as customer status, product description, price, or inventory quantity. Normalize each field's data type and decide how blanks, case, dates, and numeric tolerance should be treated before comparing it.

Use an Excel or Google Sheets join, Power Query merge, SQL JOIN, or dataframe merge to align complete records by ID. CompareTwoLists' Compare Columns tool is useful for the two extracted ID lists, but it does not join complete records or compare every field in a CSV automatically.

1. Create a combined list of IDs that are present in both exports (using the results from step 3).
2. For each row, compare field by field using IF(SourceField=TargetField, "Match", "Diff") or an array formula.
3. Optionally, concatenate all key fields and hash them to check equivalence in one cell.
4. Extract rows that have at least one field difference into a 'Changes' report.

• When comparing numeric values, beware of precision or formatting differences (e.g., 10.00 vs 10). Convert both to consistent type.
• For large files with many columns, focus on columns that are critical for your business logic; exclude irrelevant fields like timestamps that will always differ.

=IF(D2=E2, "", "DIFF")

SELECT s.CustomerID, s.CustomerName AS SourceName, t.CustomerName AS TargetName FROM Source s INNER JOIN Target t ON s.CustomerID = t.CustomerID WHERE s.CustomerName <> t.CustomerName OR (s.CustomerName IS NULL AND t.CustomerName IS NOT NULL) OR (s.CustomerName IS NOT NULL AND t.CustomerName IS NULL);

5. Build Audit Evidence: Create a Change Log

Once you know which fields changed for which IDs, create an auditable change log with the record ID, field name, source value, target value, status, and review notes. This structured report gives reviewers evidence they can filter, annotate, and approve.

Build the log with spreadsheet formulas, Power Query, SQL, or a dataframe workflow after the records are joined by ID. List-comparison output can support the missing-ID portion of the audit, but a complete field-level change log must come from a structured-data comparison.

1. For each ID that has changes, create one row per changed field.
2. Populate columns: RecordID, FieldName, SourceValue, TargetValue, Status.
3. Use conditional formatting to highlight differences (e.g., red for mismatches).
4. Add a summary sheet that shows counts of matches, changes, missing from each side.

• An audit log can be directly imported into project management tools for action items.
• Maintain a separate sheet for document assumptions (e.g., 'Timestamp differences ignored').
• Always include a header row and ensure date/time stamps for each audit run.

=IF(Sheet1!D2<>Sheet2!D2, "Changed", "")

6. Validate with List Statistics

Finish with count checks. After normalization and duplicate review, compare total rows, non-empty IDs, unique IDs, duplicate groups, and blank IDs on each side. These totals help reveal a missed filter or an accidental duplicate removal.

The List Statistics tool reports line counts, non-empty rows, unique values, duplicate groups, blank lines, and average text length. Numeric sums and field-level reconciliation still belong in Excel, SQL, or another structured-data tool.

1. Record total, non-empty, unique, duplicate-group, and blank counts for each ID list.
2. Confirm that matched plus source-only unique IDs equals the Source unique-ID count.
3. Confirm that matched plus target-only unique IDs equals the Target unique-ID count.
4. Investigate every unexplained difference before sign-off.

• Use SQL COUNT, COUNT(DISTINCT ...), and GROUP BY for database-side checks.
• Use spreadsheet sums separately when numeric totals must also reconcile.

=COUNTIF(K:K, "Match")

SELECT 'SourceCount' AS Metric, COUNT(*) AS Value FROM Source UNION ALL SELECT 'TargetCount', COUNT(*) FROM Target UNION ALL SELECT 'Matched', COUNT(*) FROM Source s INNER JOIN Target t ON s.ID = t.ID;

7. Safe Review and Sign-Off

The final step is to have a second person or an independent tool review the reconciliation for completeness and accuracy. This reduces the risk of confirmation bias. Review the missing records list—are they all valid exclusions? Spot-check a sample of the changed records to ensure the field comparison logic was error-free. For high-stakes reconciliations, consider flipping the order (swap Source and Target) to see if the same differences are reported.

Create a sign-off sheet that captures the date, reviewer name, total number of differences (by category), and any exceptions. The goal is to have a decision-quality report that allows managers to confidently approve the next step (e.g., data reload, discrepancy correction).

1. Prepare a reconciliation summary report with key metrics: total records each side, matched, unmatched, changed, unchanged.
2. Have a colleague be a second reviewer; ask them to repeat the comparison using a different method (e.g., a manual check of a random sample).
3. Document any known limitations, such as excluded columns or fuzzy matching decisions.
4. Store the final report alongside the raw exports for future reference.

• Use version control for the exports: name files with dates and suffixes like _source-v1, _target-v2.
• If reconciliation fails initial checks, return to step 1 and refine normalization or duplicate handling.