How do I find and delete duplicate values in a range of cells while keeping the first occurrence of a duplicated value in Google Sheets?

Question

Ideally, I want to be able to search through an entire sheet (or range of cells) and remove any values that have been repeated in that sheet (for example, if "2" appears in A3, B8, and D4, then I want to keep it in A3 and delete it in B8 and D4).

I normally see this problem addressed by looking at one column or row for duplicates (using the UNIQUE function) but not for an entire sheet.

How can I do this?

Answer 1

Here's some code that will do that for an entire sheet.

Please note it will work on text and numbers and it will overwrite any formulas.

function removeDuplicates() {

    var sheet = SpreadsheetApp.getActive().getActiveSheet();
    var range = sheet.getDataRange();
    var values = range.getValues();

    var traversedValues = [];

    var rowCount = values.length;
    var colCount = values[0].length;

    for (var row = 0; row < rowCount; ++row) {

        for (var col = 0; col < colCount; ++col) {

            var value = values[row][col];

            if (traversedValues.indexOf(value) > -1) {
                values[row][col] = null;
            } else {
                traversedValues.push(value);
            }
        }
    }

    range.setValues(values);
}

---

References for you

Beginner's guide to coding with Google Apps Script

For loops

if...else statements

Arrays