Duplicate Detection and Removal Formulas
Find, flag, and handle repeated records.
The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.
Find duplicates in my data with formulas. App: [SPREADSHEET_APP]. Data columns and what counts as a duplicate (same email, same name plus date, entire row): [DUPLICATE_DEFINITION]. Sample data (fake): [SAMPLE_DATA]. Provide a table: Need | Formula | Explanation. Cover: flag duplicates (=COUNTIF($A$2:$A$100, A2)>1); flag only the second and later occurrences (=COUNTIF($A$2:A2, A2)>1); duplicates on multiple columns (COUNTIFS or a combined key column); count of distinct values (SUMPRODUCT(1/COUNTIF(range, range)) with the blank-cell caveat, or COUNTA(UNIQUE(range))); list unique values (UNIQUE); keep the latest record per key (sort and then keep the first, or a MAXIFS on a date); highlight duplicates with conditional formatting; case and space normalization before comparing (TRIM, LOWER); and near-duplicates (refer to fuzzy matching). Then give a safe removal workflow: 1) copy the data; 2) add a flag column; 3) review flagged rows; 4) decide which to keep by a documented rule; 5) remove using filters or the built-in remove-duplicates tool; 6) log what was removed. Include test cases with expected counts, and warn that identical-looking records can be different people or events, so review before deleting.
Fill in before use
Optional: type your details and the prompt above updates. Nothing is saved or sent.
Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.