Thursday, July 18, 2013

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:

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 


33 comments:

  1. It works!

    I think this may also answers another's guy question from the original post.

    Thanks Kishan your the best.


    ReplyDelete
  2. Is there a way to highlight the whole row of the duplicate value instead of just the value? Essentially, I just want to highlight the rows that have duplicate values in only one specific column.

    ReplyDelete
  3. I want to compare Column A to Column E but I want my range to not include headers (aka row 1). I couldn't figure out the range format ("A2:E10") for example didn't work. I got a problem with the method/string

    ReplyDelete
    Replies
    1. Sorry for late reply... but "A2:E10" should work and also "A2:E" should also work...

      Delete
  4. Very Nice! But, how should I specify the range in case I wanna check as unique only columns A,B,F,E ?

    ReplyDelete
    Replies
    1. for such requirement, code will need to be modified... but due to lack of time I may not be able to work on your requirement soon... I'll try it as soon as I get some free time..!

      Or else what you can try is: put the range as "A:F" and then select column C and D and then change the back ground back to white...

      Delete
    2. Thanks for the reply! "Or else what you can try is: put the range as "A:F" and then select column C and D and then change the back ground back to white..." - it's not acceptable, C and D might contain different data along with the common data in others.

      Can I just use 2 different var dataRange = sheet.getRange("A:A"); statements and variables for different ranges and then combine them in one variable/data?

      Delete
    3. Hi Antony,

      Try the following code:


      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:F");
      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++) {
      if( j!=2 && j!=3 ) {
      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);
      };



      Delete
  5. Hi Kishan,

    I'm really a newbie in this field, can you tell me where the code must be inserted?
    if possible, step by step guide would be very helpful.
    thanks.

    Sardor

    ReplyDelete
  6. Is it possible to figure out how many times something is duplicated? i.e. is it possible to have it do a specific color if something is duplicated 2 times or 3 times?

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete
  8. Thank you very much for this.. it has been very helpful!.

    I think the script is case sensitive. How do I make it treat "Name" and "name" the same?

    Thanks in advance

    ReplyDelete
  9. This was exactly what I needed. I just changed it to run OnEdit and it's perfect! Thank you!

    ReplyDelete
  10. Is there an easy way to change this so that I can check a column in one document against a column in another? (or even 3 against each other?)

    ReplyDelete
  11. Hello,

    Can you tell me how can I run this script in only one sheet?

    ReplyDelete
  12. ¡Una maravilla! ¡Muchas gracias! ¡Thank you very much!

    ReplyDelete
  13. I am not familiar with these codes, I just followed the instruction and the first script I used was showing the duplicates for the whole sheet, then I changed the codes to see duplicates in just one column, but it keeps showing duplicates for the whole sheet, how can I replace the scripts? or how can I delete the script and turn everything back to the beginning?

    ReplyDelete
  14. This comment has been removed by the author.

    ReplyDelete
  15. BTW:"Method Range.setBackgroundColors is deprecated."

    ReplyDelete
  16. Hi, thanks for your nice script. Is it also possible to extend the duplicate values like this: 123456, 123456a, 123456b, 123456c, and so on?

    ReplyDelete
  17. Is it possible to leave non-duplicated cells as their original color instead of changing them to white?

    ReplyDelete
  18. Is it possible to leave non-duplicated cells as their original color instead of changing them to white?

    ReplyDelete
  19. This comment has been removed by the author.

    ReplyDelete
  20. @Anonymous
    Try the Following Code (Just changed 'WHITE' to 'NIL' in the original script):
    -------------------------------------------

    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:F");
    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++) {
    if( j!=2 && j!=3 ) {
    formats[i][j] = 'NIL';
    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]] == 'NIL') {
    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);
    }

    ------------------------------------------------
    -ATeam@Mannapovllc

    ReplyDelete
  21. i tried the code and i renamed it and saved it and tried to run it but the thing is that i can't find the Script tap!!!!

    ReplyDelete
  22. This is great! However I would like to compare two different ranges within one sheet. I'm assuming there would have to be two functions that create arrays that can be compared ate the end i.e. numValues1 and numValues2?

    ReplyDelete
  23. Great!.... Hello need your help, i want to export data in excel from the google spreadsheet then save it in c:/ drive..


    Thanks...

    ReplyDelete
  24. one more question: i want to consolidate data from the different spreadsheet to spreadsheet..

    ReplyDelete
  25. how can i inject some colors? someone help me?

    ReplyDelete
  26. Hello, I understand how to have duplicates on for a selected range. What I want- google sheets to highlight new data that I enter in that is a duplicate. Right now, it is only highlighting duplicates for the current set of data, but if a new cell is entered it does not highlight that as a duplicate. I need it to be actively highlighting duplicates as they are added. Thank you I appreciate the help!

    ReplyDelete
  27. Is it possible to remove the duplicates rather than highlight them?

    ReplyDelete