Removing 'complicated' duplicates

Question

Test File

Sometimes, my lists of emails include duplicate addresses for the same person. For example, Jane's addresses are both "jane.doe@email.com" and "doe.jane@email". Her variants include replacing the "." with "-" or "_". At the moment, my duplicates script—upgraded ever so kindly by @Jordan Running and Ed Nelson—takes care of 'strict' duplicates, yet cannot detect that "doe.jane@email.com" is a 'complicated' duplicate of "jane.doe@email.com". Is there a way to delete even these duplicates such that I do not email more than one of Jane's addresses? All of them point to the same inbox, so I need only include one of her addresses.

Here is my current code:

function removeDuplicates() {
  const startTime = new Date();
  const newData = [];
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const numRows = data.length;
  const seen = {};

  for (var i = 0, row, key; i < numRows && (row = data[i]); i++) {
    key = JSON.stringify(row);
    if (key in seen) {
      continue;
    }
    seen[key] = true;
    newData.push(row);
  };

  sheet.clearContents();
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);

  // Show summary
  const secs = (new Date() - startTime) / 1000;
  SpreadsheetApp.getActiveSpreadsheet().toast(
    Utilities.formatString('Processed %d rows in %.2f seconds (%.1f rows/sec); %d deleted',
                           numRows, secs, numRows / secs, numRows - newData.length),
    'Remove duplicates', -1);
}

Answer 1

Sample File

Fuzzy match test

Notes:

• used without @email.com part, it distorts the result
• use a the custom function: =removeDuplicatesFuzzy(B2:B12,0.66)
• 0.66 is a percentage of fuzzy match.
• the right column of a result (Column D) shows found matches with > 0.66 accuracies. Dash - is when matches are not found ("unique" values)

Background

You may try this library: https://github.com/Glench/fuzzyset.js

To install it, copy the code from here.

The usage is simple:

function similar_test(string1, string2)
{
  string1 = string1 || 'jane.doe@email.com';
  string2 = string2 || 'doe.jane@email.com'
  a = FuzzySet();
  a.add(string1);
  var result = a.get(string2);
  
  Logger.log(result);  // [[0.6666666666666667, jane.doe@email.com]]
  
  return result[0][0]; // 0.6666666666666667
}

There's also more info here: https://glench.github.io/fuzzyset.js/

Notes:

• please google more info, look for javascript fuzzy string match. Here's related Q: Javascript fuzzy search that makes sense. Note: the solution should work in Google Sheets (no ECMA-6)
• this algorithm is not smart like a human, it tests a string by char. If you have two similar strings like don.jeans@email.com it will be 84% similar to doe.jane@email.com but human detects it is completely another person.

Answer 2

Search for my Google Sheets add-on called Flookup. It should do what you want.

For your case, you can use this function:

ULIST(colArray, [threshold])

The parameter details are:

1. colArray: the column from which unique values are to be returned.
2. threshold: the minimum percentage similarity between the colArray values that are not unique.

Or you can simply use the Highlight duplicates or Remove duplicates from the add-on menu. The key feature is that you can adjust the level of strictness by changing the percentage similarity.

Bonus: It will easily catch swaps like jane.doe@email.com / doe.jane@email.com

You can find out more at the official website.