Locating duplicate data is a basic need while working with spreadsheets. Honestly, I was surprised that Google Sheets did not have a tool to locate duplicates built into it. There are a few paid add-ons. Not being impressed with them, I decided to build my own.

This project is focused on locating duplicate data within a single column. It will scan the column selected in the code and highlight any data that appears within that column multiple times.

In the future, I plan on adding a menu within Sheets to allow a user to select the row or column they would like to search for duplicates.

Plain Code Text

[code]

// Find Duplicates
// Kurt Kaiser, 2018

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

// Creates an array with data from a chosen column
function readData(){
var column = 2;
var lastRow = sheet.getLastRow();
var columnRange = sheet.getRange(1, column, lastRow);
var rangeArray = columnRange.getValues();
// Convert to one dimensional array
rangeArray = [].concat.apply([], rangeArray);
return rangeArray;
}

// Sort data and find duplicates
function findDuplicates(data){
var sortedData = data.slice().sort();
var duplicates = [];
for (var i = 0; i < sortedData.length – 1; i++) {
if (sortedData[i + 1] == sortedData[i]){
duplicates.push(sortedData[i]);
}
}
return duplicates;
}

// Find locations of all duplicates
function getIndexes(data, duplicates){
var column = 2;
var indexes = [];
i = -1;
// Loop through duplicates to find their indexes
for (var n = 0; n < duplicates.length; n++){
while ((i = data.indexOf(duplicates[n], i + 1)) != -1) {
indexes.push(i);
}
}
// Highlight all instances of duplicate values
for (n = 0; n < indexes.length; n++) {
sheet.getRange(indexes[n] + 1, column).setBackground("yellow");
}
}

//———– Main ————-
function main(){
var data = readData();
var duplicates = findDuplicates(data);
getIndexes(data, duplicates);
}

[/code]