The prompt
Score 10 of 10, Excellent
Use it when Your contact list, CRM export or sign-up sheet has the same person or item several times, and you need rules for which row to keep and formulas to mark the rest.
- {{app_and_version}}
- {{row_meaning}}
- {{match_columns}}
- {{keep_rule}}
- {{purpose}}
- {{sample_rows}}
Full prompt and why it works
Prompt
App and version: {{app_and_version}} What one row should represent: {{row_meaning}} Columns that show two rows are the same item: {{match_columns}} Which row to keep when there are duplicates: {{keep_rule}} What the cleaned list is for: {{purpose}} Headers and sample rows, including some duplicates: """ {{sample_rows}} """ Task: Give me a way to find duplicates in {{app_and_version}} and mark which row to keep, so that the cleaned list is fit for {{purpose}}. I want to review the marks before anything is deleted, so nothing should be removed automatically. Format: 1. Match key: a helper column formula that builds a clean key from the matching columns (trimmed, lower case, and for phone numbers digits only), so near-duplicates such as "Ann Lee " and "ann lee" match. 2. Duplicate count: a formula showing how many rows share each key. 3. Keep or Remove: a formula that marks one row per key "Keep" and the others "Remove", following my keep rule, for example the most recent date or the row with the most fields filled. Rows with a blank key are marked "No key" for review, never "Remove", since blanks would otherwise all match each other. 4. For each formula: the formula in a code block, the column it goes in, and two plain sentences on what it does. 5. Check it: walk through one group of duplicates from my sample and show which row is kept and why. 6. Before deleting: copy the original to a backup tab, filter on "Remove", review, then delete; and how to carry useful details from removed rows (a phone number only the older row has, say) into the kept row by hand. 7. False matches: cases where the key would join two different people or items, such as two people sharing one email address, and how to spot them. Requirements: - Only functions in {{app_and_version}}; no macros. - Under 450 words, plus formulas. - Do not make the built-in Remove Duplicates command the main method, because it keeps the first row it meets, not the right one. Mention it only as a final step after review. Example of a check's form only: "Key ann.lee@example.com: rows 4 and 19. Row 19 has the later date (June 3), so row 19 is Keep and row 4 is Remove." If my keep rule does not decide between two rows, for example when they have the same date, say what the formula does in a tie and suggest a second rule.
Variables
- {{app_and_version}}
- For example "Excel for Microsoft 365" or "Google Sheets".
- {{row_meaning}}
- For example "one customer", "one order" or "one applicant".
- {{match_columns}}
- For example "email", or "first name, last name and postcode".
- {{keep_rule}}
- For example "the most recent Last updated date" or "the row with a phone number".
- {{purpose}}
- For example "a mailing to members" or "importing into our new CRM".
- {{sample_rows}}
- Headers and ten or so rows that include duplicates, with real names and contact details replaced by placeholders.
Why it works
- What to avoid. Nothing is deleted automatically: rows are marked Keep or Remove for review, and the built-in Remove Duplicates command waits until the end because it keeps the first row, not the right one.
- If unsure. Ties under the keep rule and keys that could join two different people are named, with a second rule or a way to spot them.
- An example. One duplicate group from your own sample is walked through, so you can see the formulas pick the right row before you trust them on the whole list.