Prompt: compare two lists or Excel sheets without missing a row
Five rules that make any AI match rows by a key, treat 0 as a value and keep near matches (lost zeros, trailing spaces) for you to decide. Tested for real on two stock counts: every row classified once.
Works with: ChatGPT, Claude, Gemini, Copilot
The task
Find what was added, removed and changed between two versions of a list (stock counts, price lists, contact lists), without matching rows by position.
Compare these two lists. Rules: 1. Match rows by exact [KEY, e.g. SKU or invoice number], never by row position. Keep every [KEY] as text, exactly as written. 2. A quantity of 0 is a real value, not a missing row. 3. Report four lists: ADDED, REMOVED, CHANGED (with before → after) and UNCHANGED. 4. If two [KEY]s differ only by spaces, capital letters or leading zeros, don't merge them: list them under POSSIBLE MATCH for me to decide. 5. Don't rewrite or correct either list. [NAME OF LIST 1, e.g. MONDAY] [PASTE LIST 1 WITH ITS HEADER ROW] [NAME OF LIST 2, e.g. FRIDAY] [PASTE LIST 2 WITH ITS HEADER ROW]
How to use it
- Replace [KEY] with the column that identifies each row: a SKU, an invoice number, an email.
- Paste both lists with their header rows. Keep it short: a few dozen lines.
- Run it in your approved AI assistant and read the POSSIBLE MATCH section first.
- For a big export, ask for the formula instead. Full test in How to compare two Excel sheets.
What you get
Four lists (ADDED, REMOVED, CHANGED with before → after, UNCHANGED), near matches listed separately with the reason, and a count that adds up on both sides.
## Stock count comparison: Monday → Friday Rows were matched by exact SKU text. Row order made no difference. ### ADDED (on Friday only) | SKU | Item | Qty | |---|---|---| | `P104` | Teflon tape | 25 | ### REMOVED (on Monday only) | SKU | Item | Qty (Monday) | |---|---|---| | `P103` | Ball valve 1 in | 0 | *P103 had a real quantity of 0 on Monday. Its row is missing on Friday, so it counts as removed.* ### CHANGED | SKU | Item | Before → After | |---|---|---| | `P102` | PEX tubing 3/4 in | 9 → 8 | | `P106` | Shut-off valve 1/2 in | 12 → 0 | *P106 is still on the Friday list with a quantity of 0, so it is a change and not a removal.* ### UNCHANGED | SKU | Item | Qty | |---|---|---| | `P101` | Copper elbow 1/2 in | 40 | | `P105` | Pipe wrench 14 in | 6 | | `P108` | Drain snake 25 ft | 3 | ### POSSIBLE MATCH (your call) | Monday SKU | Friday SKU | Difference | Item | Qty Mon / Fri | |---|---|---|---|---| | `"0412"` | `"412"` | Leading zero | Brass fitting 1/2 in | 30 / 30 | | `"P107"` | `"P107 "` | Trailing space on Friday | Thread sealant | 15 / 15 | I left these two pairs out of the four lists above until you decide: - **If they're the same item:** each pair goes under UNCHANGED, since the item name and quantity are identical. - **If they're different SKUs:** `0412` and `P107` go under REMOVED, and `412` and `P107 ` go under ADDED. ### Check - Monday: 8 rows = 3 unchanged + 2 changed + 1 removed + 2 possible matches - Friday: 8 rows = 3 unchanged + 2 changed + 1 added + 2 possible matches Neither list was edited. SKUs appear exactly as written, including the trailing space in `"P107 "`.
Go further
- How to compare two Excel sheets and highlight the differences (with AI, tested in Excel)Compared row by row, 6 of 8 lines looked changed: only 2 had, and a product with zero stock vanished unnoticed. A prompt for any AI, an AI-written formula we tested in Excel, and a weekly Power Query set-up.Read the article
- Prompt: get the Excel formulas that compare two sheets (and test them)Ask the AI for a status column (REMOVED, CHANGED, SAME), an ADDED flag and a highlight rule, where a missing row can never come back as 0. The real answer, tested in Excel on eight tricky rows.See the prompt
Where should you start with AI?
The free assessment gives you, in 4 minutes, the 3 tasks to automate first in your business.