Tuesday, September 4, 2012

How to check duplicate values in a column and color these cells

NOTE:

I have updated the script so that you can easily change the range, so now it can work to check duplicates in any column or any row or any range:


==================

Question:

(by Kylecore)




It looks exactly like something i need for a task I've been working on except I'm slightly confused on how to implement it and i need a few more specifics.

I have a few things I'd need slighty different however.
One is that i have columns "A"-"I" already in use.
I still only need to check for duplicates in column "A" only but would prefer for it to simply colour the box or text within column "A" instead of putting a colour or word in another column like "J".
I need to compare up to 1000 rows at some times and I do NOT want anything deleted.
I would like to be shown where the duplicates are for me to remove later.
The data being compared in column A is solely numbers with no text, not sure if it makes a difference.
Is there any way this can be done
I've also included an example image to show you what i am requsting

Currently i have a filter set up to remove certain things from my list but no idea on how to accomplish the above.
Any response at all would be greatly appreciated, and thank you very much for reading.


Solution:

Here is the script that will look into the duplicate values in Column A, and it will color these duplicate cells to "YELLOW" color (you can change this color to whatever you want).

Note:
Before running this function (script) you have to first change the color of whole Column A to white color or any other color, so that if you have already "YELLOW" color to some cell and if it is not a duplicate then this script is not going to change to white color, so you have to take care of it.

And this script will take a little bit of your time to execute, so you have to be patient.

Here is the script:



function onOpen() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var menuEntries = [ {name: "Check Duplicates", functionName: "myFunction"}];
 ss.addMenu("Scripts", menuEntries);
};

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1"); //change it to whatever your sheet name is

  var datarange = sheet.getDataRange();
  var rows = datarange.getLastRow();
  
  
  for(i=0;i<rows-1;i++)
  {
    var var1 = datarange.offset(i, 0, 1, 1).getValue();
    
    if ( var1 != "" )
    {   
      var k  = i + 1;
    
      for(j=k;j<rows;j++)
      {
        var var2 = datarange.offset(j, 0, 1, 1).getValue();
      
        if( var1 == var2 )
        {
          datarange.offset(i, 0, 1, 1).setBackground("YELLOW");
          datarange.offset(j, 0, 1, 1).setBackground("YELLOW");
        };
      };
    };
  };

}


If you are new to Google Spreadsheet Scripts, then please have a look at the following link:


I hope this script will help you, and if you need more help then please do comment below on this blog itself.

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

Thanks,
Kishan,

5 comments:

  1. It doesn't seem to be working
    Little confused as to what to do as script manager is adding two things with the exact same coding and different titles?

    ReplyDelete
    Replies
    1. Hi Kyle, you have to go to "Script editor" from the "Tools" menu and you need to delete all the contents of the script (if you haven't coded anything in it), then you need to copy my script and paste it in the script editor and then save it and run the function "Check Duplicates"

      I hope this will work.

      You can also run this script by clicking on "myFunction" from the "Scripts" menu that is near "Help" menu at the menu bar.

      Have a look at the following link of a spreadsheet:
      https://docs.google.com/spreadsheet/ccc?key=0AmMTqpzD9YRndEJveVJxdkFOZjRXbG54YjdnUE1jQWc#gid=0

      Let me know whether it worked now or still not.

      Delete
  2. This script will only search Column A for duplicates? or do I need to specify Column A somewhere in the script?

    ReplyDelete
    Replies
    1. Hi,

      Your script helps me a lot. I've got a couple of questions.
      1.) How can you customize the column location where to search duplicates from in the script editor.

      2.) How can the script integrated in forms.

      Hoping for an optimistic response.

      Thanks!

      Delete