Google Spreadsheet How to check Duplicates in any Range (any Row or any Column or any Range)

Following is the Script to check duplicates in any range (that is any column like "A:A" or any row like "1:1" or any other range "B2:G43":

//================================

function onOpen() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet();

  var entries = [{name : "Check Duplicates",functionName : "checkDuplicates"}];

  sheet.addMenu("Scripts", entries);

};

function checkDuplicates() {

  var sheet = SpreadsheetApp.getActiveSheet();

  var dataRange = sheet.getRange("A:A"); // Set Any Range

  // "A:A" is for Column A

  // And if you want to check duplicates for whole sheet then try this:

  // var dataRange = sheet.getDataRange();

  var data = dataRange.getValues();

  var numRows = data.length;

  var numColumns = data[0].length;

  var formats = [];

  var values = [];

  for (var i = 0; i < numRows; i++) {

    formats[i] = [];

    for (var j = 0; j < numColumns; j++) {

      formats[i][j] = 'WHITE';

      if (data[i][j] != '') {

        values.push([data[i][j], i, j]);

      }

    }

  }

  var numValues = values.length;

  

  for (var k = 0 ; k < numValues - 1; k++) {

    if (formats[values[k][1]][values[k][2]] == 'WHITE') {

      for (var l = k + 1; l < numValues; l++) {

        if (values[k][0] == values[l][0]) {

          formats[values[k][1]][values[k][2]] = 'RED';

          formats[values[l][1]][values[l][2]] = 'RED';

        }

      }

    }

  }

    

  dataRange.setBackgroundColors(formats);

}

//================================

Put the above code in your Spreadsheet's Script editor. After putting this code, refresh your spreadsheet once, so that you can see the "Check Duplicates" in Menu Bar under "Scripts" Menu

You can run the above script from Menu bar, have a look at the following screenshot:

And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive