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:

http://igoogledrive.blogspot.com/2013/07/how-to-check-duplicates-in-any-range.html

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

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:

http://igoogledrive.blogspot.in/2012/08/how-to-write-script-in-google.html

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,

iGoogleDrive