Highlight duplicates and copy to new sheet

Question

I have a large sheet of server names, ~12,000 rows. I need to highlight any duplicate server names and then copy them to a new sheet. I have a script to highlight the cells, that works great (thank you Kurt Kaiser). But, I can't figure out how to write the duplicates (all instances) to a new sheet. Here's the snippet of the code that highlights the duplicates.

// Highlight all instances of duplicate values in a column
function highlightColumnDuplicates(indexes) {
  var column = 1;
  for (n = 0; n < indexes.length; n++) {
    sheet.getRange(indexes[n] + 1, column).setBackground("yellow");
  }
}

Any help would be appreciated.

user_11732320 · Answer

You don't need to use Google App Scripts to create a duplicates list to a new sheet. This function would do it for all values in a column a. =Unique(filter(List!A:A,not(isblank(List!A:A)),Countif(List!A:A,List!A1:A)>1))

You can see an example here.

However if you do want an app scripts solution, the below code should also work. The same sample google sheet also shows this on a different tab.

/**
 * @OnlyCurrentDoc
 */
function listDuplicates(someRange) {
  var firstList = someRange;
  var singleList = [];
  var dupList = [];

  for (var i = 0; i < firstList.length; i++) {
    var aRow = firstList[i];
    for (var j = 0; j < aRow.length; j++) {
      var theOutput = aRow[j];
      if (theOutput != '') {
        if (singleList.includes(theOutput)) {
            dupList.push(theOutput);
        } else {
            singleList.push(theOutput);
        }
      }
    }
  }
  return Array.from(new Set(dupList));
}

user_14265469 · Answer

Just in case here is another solution:

function main() {
  const duplicates = highlightColumnDuplicates();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dest_sheet = ss.getSheetByName('Duplicates') || ss.insertSheet('Duplicates');
  const dest_range = dest_sheet.getRange(1,1,duplicates.length,1);
  dest_range.setValues(duplicates.map(x=>[x]));
}

function highlightColumnDuplicates() {

  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getRange("A1:A" + sheet.getLastRow());
  const data  = range.getValues().flat();  

  // returns true if 'x' is repeated in 'arr'
  const is_duplicate = (x,arr) => arr.indexOf(x) != arr.lastIndexOf(x);

  // get indexes (row numbers) of duplicates in data (in the column)
  const indexes = data.map((x,i) => is_duplicate(x,data) ? i : '').filter(String);

  // change backgrounds like a sir
  const colors = range.getBackgrounds();
  indexes.forEach(x => colors[x][0] = "yellow");
  range.setBackgrounds(colors);

  // get duplicates from the data via indexes
  const duplicates = Array.from(new Set(indexes.map(x => data[x])));  

  return duplicates;
  
}

It will make yellow background for all cells with duplicates on current sheet in column A and copy the duplicates on a sheet with name Duplicates.

Update

If you need to get full rows and all instances of the duplicates here is the modified code:

function main() {
  const duplicates = highlightColumnDuplicates();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dest_sheet = ss.getSheetByName('Duplicates') || ss.insertSheet('Duplicates');
  const dest_range = dest_sheet.getRange(1,1,duplicates.length,duplicates[0].length);
  dest_range.setValues(duplicates);
}

function highlightColumnDuplicates() {

  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getRange('A1:A' + sheet.getLastRow());
  const data  = range.getValues().flat();  

  // returns true if 'x' is repeated in 'arr'
  const is_duplicate = (x,arr) => arr.indexOf(x) != arr.lastIndexOf(x);

  // get indexes (row numbers) of duplicates in data (in the column)
  const indexes = data.map((x,i) => is_duplicate(x,data) ? i : '').filter(String);

  // change backgrounds like a sir
  const colors = range.getBackgrounds();
  indexes.forEach(x => colors[x][0] = "yellow");
  // range.setBackgrounds(colors);

  // get duplicates from the data via indexes
  const data_all  = sheet.getDataRange().getValues();
  const duplicates = indexes.map(x => data_all[x]);

  return duplicates;  
}

Highlight duplicates and copy to new sheet

2 Answers

Update