How to use free AI to clean up messy vendor and catalog data

Every distributor has a spreadsheet that's a mess: a supplier price list pasted from a PDF, a customer's part numbers that don't line up with yours, an export with the header in the wrong row and prices formatted as text. Cleaning it by hand is an afternoon. Free AI can do most of it in minutes — if you know what to ask. Here's a practical walkthrough, no formulas memorized.

The three tools you'll use (all free)

• A chat assistant (ChatGPT, Claude, or Gemini free) for pasting small tables and asking for cleanup, or for generating a formula.
• A free AI spreadsheet helper like Formula Bot, which turns "total quantity per customer, ignore blanks" into a working formula, or generates a starter sheet from a text description.
• Copilot suggestions in Excel if you have Microsoft 365, which explain and write formulas in the sidebar.

One caution before you start: a supplier price file with real costs is business-sensitive. For anything confidential, use the formula-generation approach (you ask for the formula, you keep the data local) rather than pasting the whole file into a public chatbot.

Job 1: the header is in the wrong row

Exports love to put three rows of junk above the real column names. Instead of deleting by hand, describe it: "My data has titles in rows 1–3 and the real headers in row 4. Give me the steps to promote row 4 to headers and drop the rest in Excel." You get click-by-click steps. For a repeatable version, ask for the Power Query steps.

Job 2: prices stored as text won't add up

The classic: a column of prices that won't sum because they're text with a stray "$" or a trailing space. Ask: "Column C has prices like '$12.50 ' stored as text. Give me a formula to convert them to real numbers." The AI hands you a VALUE(SUBSTITUTE(...)) pattern and explains it. Paste, fill down, done.

Job 3: matching customer part numbers to your SKUs

This is the one that eats real time. A customer uses their own part numbers; you need your SKU. If you have a cross-reference table, AI writes the lookup for you: "I have customer part numbers in column A and a cross-reference table on Sheet2 (their number in column A, my SKU in column B). Write a formula to pull my SKU into column B." You get an XLOOKUP or VLOOKUP with the ranges filled in.

The honest limit: AI writes the lookup, but it can't invent a cross-reference that doesn't exist. If you don't have a mapping table, no formula will match "their number" to "your number." That gap — matching a customer's part numbers to your catalog on every order — is exactly what a PO automation tool is built to learn and remember, so you're not rebuilding the lookup each time.

Job 4: de-duplicate and standardize

Supplier lists arrive with "Inc.", "inc", and "INC" as three different vendors. Ask the assistant for the steps to standardize casing and remove duplicates, or paste a short column and ask it to return a cleaned, de-duplicated version. For a few hundred rows this is faster than doing it manually and far less error-prone.

Job 5: build a simple tracker from scratch

Need a basic reorder tracker or a quick inventory count sheet and don't want to design one? Describe it in plain English to a free AI spreadsheet generator — "an inventory sheet with SKU, description, on-hand, reorder point, and a column that flags when on-hand is below the reorder point" — and download the starter file. QuickBooks also publishes free Excel inventory templates worth grabbing.

A repeatable rule of thumb

Use AI for the shape of the work — the formula, the steps, the cleanup pattern — and keep the sensitive data under your control. When you find yourself doing the same cleanup on the same kind of file every week (a supplier's recurring price update, every customer's incoming order), that's the signal to stop cleaning and start automating the source. A spreadsheet you fix by hand every Monday is a process that wants a tool.

Where this connects to order entry

Most of the spreadsheet pain at a distributor traces back to one place: data arriving in a format that doesn't match your system. The cleanest fix isn't a better spreadsheet — it's catching the data at the door. SideQuest reads inbound POs, matches every line to your QuickBooks Online catalog using cross-references it learns over time, and drafts the order — so the messy-spreadsheet step never happens for the work that repeats. Run your numbers in the savings calculator.

FAQ