google-sheets - 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?

标签： google-sheets

Answer 1

这里有一些代码可以对整个工作表执行此操作。

请注意，它适用于文本和数字，并且会覆盖任何公式。

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);
}

---

给你参考

使用 Google Apps 脚本进行编码的初学者指南

对于循环

if...else陈述

数组