Highlight Duplicates and Near Matches with Apps Script

Google Sheets conditional formatting can highlight exact duplicates quickly. For near matches, you can combine helper columns, formulas or Apps Script to score similarity and flag rows for review. This guide covers practical options and where Flookup custom functions can help.


Highlight exact duplicates

Select the range, open Format > Conditional formatting, choose Custom formula is and use a formula such as =COUNTIF($A$2:$A, $A2)>1 for a single column. Adjust the range for your data. This highlights repeated values but does not detect typos or spelling variations.


Flag near matches with Apps Script

For fuzzy review, write similarity scores or group labels to a helper column, then apply conditional formatting based on that column. The example below normalises values and marks exact duplicates after basic cleanup; you can extend it with your own comparison logic.

function markPotentialDuplicates() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("A2:A");
  var values = range.getValues();
  var seen = {};
  var flags = values.map(function (row) {
    var raw = String(row[0] || "");
    var key = raw.toLowerCase().replace(/\s+/g, " ").trim();
    if (!key) return [""];
    if (seen[key]) return ["Possible duplicate"];
    seen[key] = true;
    return [""];
  });
  sheet.getRange(2, 2, flags.length, 1).setValues(flags);
}

After the helper column is filled, add conditional formatting to highlight rows where the flag is not empty. This keeps the visual highlight in Sheets while the script handles the comparison logic.


Review before deleting

Highlighting is a review step, not a deletion step. Keep original data intact, inspect grouped or flagged values and confirm which record to keep before removing rows. For important datasets, work on a copy or write results to a review sheet.


Using Flookup custom functions

Flookup custom functions can support duplicate review workflows in formulas. FUZZYSIM compares two values and returns a similarity score, FLOOKUP can find close matches in a table and DEDUPE or ULIST can help identify or extract unique values based on your criteria. See custom functions for syntax and examples. You can also use the Sheets AI Assistant to get formula suggestions for your sheet.