Data Validation and Cleanup for Product Lists

Our company, Assembly Required Distributors (ARD), needs assistance validating and cleaning product part-number data across three separate systems. We have provided three Excel files, each representing product lists from a different system. ARD’s QuickBooks file is the master record, and the other two files contain what Creative Living/McCullough and Ocelco/McCullough believe our part numbers are.

Each system uses its own item numbers, but within ARD’s QuickBooks environment we store the Creative and Ocelco item numbers as custom fields. Over time, some of those custom fields are incorrect or outdated. Our goal is to compare each dataset against ARD's master list and identify which part numbers in QuickBooks need to be updated.

You will be working with these files:

ARD PULL WITH CUSTOM FIELDS.xlsx
This file contains our QuickBooks item list. Column A contains the ARD master item numbers. Additional columns show the Creative and Ocelco reference numbers currently stored in QuickBooks.

Creative Product from ARD.xlsx
This file contains Creative Living/McCullough’s product list. Column A shows Creative’s master part number, and Column B shows the ARD part number they believe corresponds to it.

Ocelco Products from ARD.xlsx
This file contains Ocelco/McCullough’s product list. Column A shows Ocelco’s master part number, and Column B shows the ARD part number they believe corresponds to it.

Your job is to compare the datasets and determine whether Creative’s and Ocelco’s reference numbers match what’s stored in QuickBooks. Using ARD’s master item number as the key, we need to confirm two things for each ARD item:

Does the Creative part number shown in Creative’s file match the Creative number stored in QuickBooks?

Does the Ocelco part number shown in Ocelco’s file match the Ocelco number stored in QuickBooks?

For each comparison, identify whether the part numbers match or do not match. If they do not match, we need to know what the correct number should be. The final deliverable should include a clean Excel workbook that contains:

A complete comparison sheet with one row per ARD item, showing what’s in ARD QuickBooks, what Creative and Ocelco say the value should be, and whether the values match.

A “change list” sheet showing only the items that require correction, consisting of:

the ARD item number,

which system needs updating (Creative custom field or Ocelco custom field in QuickBooks),

the incorrect value currently in QuickBooks,

the correct replacement value from the vendor file.

This change list will later be imported by us into QuickBooks using Sassant or Transaction Pro, so it must be formatted cleanly and without extra columns.

ARD item numbers should never be changed during this process—they are our source of truth. Only Creative and Ocelco reference numbers may need updating. If you encounter part numbers that do not clearly match, or if multiple vendor part numbers map to the same ARD item, flag those in a separate tab labeled “REVIEW NEEDED.”

The project is considered complete when we receive a cleaned Excel workbook containing:

a full comparison sheet,

a mismatches-only change list, and

a brief summary of totals (number of matches, mismatches, and unresolved items).