The prompt
Score 10 of 10, Excellent
Use it when You have two lists, such as last month's and this month's customers or a system export and your own tracker, and need what is in one but not the other and what changed.
- {{app_and_version}}
- {{list_a}}
- {{list_b}}
- {{match_key}}
- {{compare_columns}}
- {{purpose}}
- {{sample_rows}}
Full prompt and why it works
Prompt
App and version: {{app_and_version}} List A (sheet name, columns, roughly how many rows): {{list_a}} List B (the same details): {{list_b}} The column that identifies the same item in both: {{match_key}} Columns to compare for changes: {{compare_columns}} Why I am comparing them: {{purpose}} A few sample rows from each list, including any I suspect: """ {{sample_rows}} """ Task: Give me formulas that compare the two lists in {{app_and_version}}, so that I can see which items are only in A, only in B, and in both but with different values. I will use the result for {{purpose}}, so a missed match matters more than a slow formula. Format: 1. Before you start: anything in the sample that would make matches fail, such as extra spaces, different capitals, IDs stored as numbers in one list and text in the other, or lost leading zeros. Give a fix for each in a new helper column, leaving the original untouched. 2. Formula 1, in a new column of List A: "In both" or "Only in A". 3. Formula 2, in a new column of List B: "In both" or "Only in B". 4. Formula 3, for items in both: which of the compared columns differ, or "Same". 5. For each formula: the formula in a code block, the cell it goes in, how to fill it down, and what it does in two or three plain sentences. 6. Check it: walk through three sample rows (one only in A, one only in B, one in both with a change) and give the result each formula should show. 7. Duplicates: how to find IDs that appear more than once in either list, since a duplicate can hide a mismatch. Requirements: - Use only functions available in {{app_and_version}}. If the version lacks XLOOKUP or FILTER, use COUNTIF or MATCH. - No macros or add-ons. - Under 450 words, plus formulas. A good result is one where every row in both lists ends up with exactly one label, and a changed value can never pass as "Same". Example of one check row's form only: "A row 7, ID 3381: found in B row 12, Status differs ('Active' and 'Paused'), so Formula 3 shows 'Status'." If it is not clear which column identifies the same item in both lists, or no single column does, say so and suggest a combined key, for example name plus postcode.
Variables
- {{app_and_version}}
- For example "Excel for Microsoft 365" or "Google Sheets".
- {{list_a}}
- For example "Sheet 'Sept', columns A to F, about 2,400 rows".
- {{list_b}}
- For example "Sheet 'Oct', columns A to G, about 2,550 rows".
- {{match_key}}
- For example "Customer ID in column A of both" or "email address".
- {{compare_columns}}
- The columns that might change, for example "Plan and Status". Write "none" if you only need missing items.
- {{purpose}}
- For example "finding customers who cancelled" or "checking the payroll export against HR records".
- {{sample_rows}}
- Headers and five rows from each list, with personal data replaced by placeholders.
Why it works
- Background facts. Sample rows from both lists expose what silently breaks matching, such as trailing spaces or IDs stored as text in one list, before any formula is written.
- An example. Three worked rows, one only in A, one only in B and one changed, let you test every formula on rows you can check by eye.
- If unsure. If no single column identifies an item in both lists, it says so and builds a combined key instead of matching on something unreliable.