Google Sheets – How to Detect Duplicates Across Multiple Sheets

conditional formattingformulasgoogle sheetsgoogle-sheets-data-validationregex

I have found a couple of formulas/scripts for what I am looking for, but they are all locked for one sheet only, thus I will try to explain what exactly I need. I am unable to do it my self, because of my lack of knowledge, so I plead to you to help me out.

I want to have multiple sheets in Google spreadsheet, they can go up to sheet1 to sheet 15, not sure about the number, but they are going to be multiple, and will multiply in the future. So in all the sheets except Sheet1 I will have emails(data), the sheet1 Column D is where in the future I will enter emails, now I want to prevent duplicate entries in Sheet1 collum D by looking in all the sheets I have in that spreadsheet. This is an example if I did not make my self clear sheet1 column D I insert email, press enter, in a case in any other sheet there is that email, I want to show an error, and inability to input the email.

Let me know if this makes sense, and if this is even possible,

This is something that I worked with fine, but it is only for sheet one and column:

=COUNTIF($D$2:$D$D,D2)<1

Also, this is something I tried, to at least mark duplicates, and it did not work.

=countif(indirect("sheet2!A:A"),E2)>1

Best Answer

Here is an example (for illustration) with Conditional Formatting and 3 sheets. The same formula can be used in Data Validation to disable entering of repeating emails.

=(COUNTIF(D:D,D:D)>1)+(REGEXMATCH(D1,TEXTJOIN("|",1,UNIQUE({INDIRECT("Sheet2!D2:D");
                                                            INDIRECT("Sheet3!D2:D")}))))

spreadsheet demo

Related Solutions

Google-sheets – Insert column across multiple sheets

You could always create the column in one sheet, and copy-paste it to the other sheets, one sheet at the time. But I guess that's what you're trying to avoid.

It is possible to do this with a Google Apps Script. The following script adds a menu in a spreadsheet, which allows you to insert or remove a column on all sheets.

function addColumnOnAllSheets() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var colIdxStr = Browser.inputBox("Insert column right of column no");
  if (colIdxStr == 'cancel') {
    return;
  }
  var colIdx = parseInt(colIdxStr);
  if (colIdx == NaN) {
    Browser.msgBox("You must enter a number or 'cancel'");
    return;
  }
  var sheets = spreadsheet.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    sheet.insertColumnAfter(colIdx);
  }
}

function removeColumnOnAllSheets() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var colIdxStr = Browser.inputBox("Remove column no");
  if (colIdxStr == 'cancel') {
    return;
  }
  var colIdx = parseInt(colIdxStr);
  if (colIdx == NaN) {
    Browser.msgBox("You must enter a number or 'cancel'");
    return;
  }
  var sheets = spreadsheet.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    sheet.deleteColumn(colIdx);
  }
}

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [
    {name: "Add column on all sheets", functionName: "addColumnOnAllSheets"}, 
    {name: 'Remove column on all sheets', functionName: 'removeColumnOnAllSheets'}
  ];
  sheet.addMenu("Columns", entries);
}

To install this script in your own spreadsheet, click Tools -> Script editor, and paste the code into the script editor. Save the script, and reload the spreadsheet in order to show the menu.

I have set up an example spreadsheet to demonstrate the script.

Google Sheets – Set Selection Across All Sheets

The closest thing you can have is the following script.

Code

function onOpen() {
  SpreadsheetApp.getUi().createMenu("Harmonize")
    .addItem("Go !!", "mySheets").addToUi();
}

function mySheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // retrieve number of sheets
  var nshs = ss.getNumSheets();

  // find first sheet
  for(var i=0; i<nshs; i++) {
    var sh = ss.getSheets()[i];
    if(sh.getIndex() == 1) {
      var aCell = ss.getActiveCell().getA1Notation();
      var index = i;
    }
  }

  // set active range throughout the sheets
  for(var j=0; j<nshs; j++) {
    if(ss.getSheets()[j].getIndex() != 1) {
      ss.getSheets()[j].setActiveSelection(aCell).activate();
      Utilities.sleep(200);
    }
  }

  // return to first sheet
  ss.getSheets()[index].setActiveSelection(aCell).activate();   
}

Explained

Upon opening, a menu item is created. Selecting this item, will execute the mySheet function. This will find the first sheet (doesn't need to be getSheets[0]) and its index plus the A1Notation(). After that, it will set the active selection of each sheet according to the first sheet.

Example

I've created an example sheet for you: Set Selection throughout all sheets