DUPLICATES Function for Google Sheets

The custom function DUPLICATES for Google Sheets returns the unique list of duplicates from a range of cells as an array (sorted in alphabetical order).

Simply copy and paste the code of the DUPLICATES function into the script editor to be able to use it later (for more details, check out the Add a Custom Function to Google Sheets page).


Usage Example

The DUPLICATES function is used here to list the duplicates from the cell range A2:B15:

=DUPLICATES(A2:B15)

Preview:

google sheets function duplicates

Google Apps Script Code of the Function

The code of the function to copy-paste into the script editor:

function DUPLICATES(values) {
  
  // Source : https://www.sheets-pratique.com/en/codes/duplicates-function
  
  // Range on one column
  let array = values.flat();
  
  // Removal of empty values
  array = array.filter(x => x !== '');
  
  // Searching for duplicates
  let duplicates = [];
  for (const value of array) {
    let count = 0;
    for (const value2 of array) {
      if (value === value2) {
        count++;
      }
    }
    // If a new duplicate
    if (count > 1 && duplicates.indexOf(value) == -1) {
      duplicates.push(value);
    }
  }
  
  // Alphabetical sorting
  duplicates.sort();
  
  // Returns a unique and sorted list of duplicates
  return duplicates.length ? duplicates : '';
}

Or this equivalent and shorter version of the DUPLICATES function (but less readable):

function DUPLICATES(t) {
  // Source : https://www.sheets-pratique.com/en/codes/duplicates-function
  t = t.flat().filter(i => i !== '');
  t = new Set(t.filter(i => t.filter(j => i === j).length > 1).sort());
  return t.size ? [...t] : '';
}