App Script to find Duplicate and Highlight cell that has duplicate value

[hbo one]

Hello Guys, 

Hope everyone is going well. I need a help with my school project. 

I need to add a script that can find and Highlight all duplicates values and highlight them. Please help me.

Link to google sheet : https://docs.google.com/spreadsheets/d/1Y_QBvSIn5TIS3N-YOMKICFU67ZAeVOji9xKugu9k3as/edit#gid=0

Here is my script : (i found it online, I am not the author)

*******************

/**

 * Finds duplicate rows in the active sheet and colors them red,

 but only pays attention to the indicated columns.

 */

function findDuplicates() {

  // List the columns you want to check by number (A = 1)

  var CHECK_COLUMNS = [5];

    

  // Get the active sheet and info about it

  var sourceSheet = SpreadsheetApp.getActiveSheet();

  var numRows = sourceSheet.getLastRow();

  var numCols = sourceSheet.getLastColumn();

  // Create the temporary working sheet

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var newSheet = ss.insertSheet("FindDupes");

  

  // Copy the desired rows to the FindDupes sheet

  for (var i = 0; i < CHECK_COLUMNS.length; i++) {

    var sourceRange = sourceSheet.getRange(1,CHECK_COLUMNS[i],numRows);  //changing to cells

    var nextCol = newSheet.getLastColumn() + 1;

    sourceRange.copyTo(newSheet.getRange(1,nextCol,numRows)); // changing to cells

  }

  

  // Find duplicates in the FindDupes sheet and color them in the main sheet

  var dupes = false;

  var data = newSheet.getDataRange().getValues();

  for (i = 1; i < data.length - 1; i++) 

  {

    for (j = i+1; j < data.length; j++) {

      

      if  (data[i].join() == data[j].join()) {

        dupes = true;

         sourceSheet.getRange(i+1,1,1,numCols).setBackground("red");

         sourceSheet.getRange(j+1,1,1,numCols).setBackground("red");

        }

    }

  }

  // Remove the FindDupes temporary sheet

  ss.deleteSheet(newSheet);

  

  // Alert the user with the results

  if (dupes) {

    Browser.msgBox("Possible duplicate(s) found and colored red.");

   // Browser.msgBox("this is test")

  } else {

    Browser.msgBox("No duplicates found.");

  }

};

/**

 * Adds a custom menu to the active spreadsheet

 */

function onOpen() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet();

  var entries = [{

    name : "Find Duplicates",

    functionName : "findDuplicates"

  }];

  sheet.addMenu("Custom Menu", entries);

};

Any help is greatly appreciated.

[Tanaike]

I saw your sample Spreadsheet. But, unfortunately, I couldn't understand your expected goal. I apologize for this. Can I ask you about the detail of your goal? For example, in order to correctly understand your question, can you provide the sample input and output situations you expect as the images?

[hbo one]

Good Morning Tanaike, 

thanks for your time. Please see details below. 

- Script should do following:

     Compare Data In Column "E" against Column "C".

           - if there are duplicate highlight all duplicate cells (NOT ENTIRE ROW) in column "E" and "Column "C"

           - if cell are blank then ignore them.

Thanks you

[Tanaike]

Thank you for replying. I would like to support you. But, I have to apologize for my poor English skill, again. Unfortunately, I cannot still understand your question. But I would like to try to understand it. When I could correctly understand it, I would like to think of a solution. I would be grateful if you can forgive my poor English skill.

[Ed Sambuco]

I think he just wants "highlights" if column A and column C in a row have the same contents.

Hbo -- open spreadsheet, set up a loop from 2 through sheet.getLastRow(), and then sheet.getRange(i,1).getValue() and getRange(i,3).getValue()

(i is row iterator) to see if they match.  If they do, highlight as wanted by one of the set ... methods in the Range class.

On Thu, Sep 22, 2022 at 11:23 PM Tanaike <kanshi...@gmail.com> wrote:

Thank you for replying. I would like to support you. But, I have to apologize for my poor English skill, again. Unfortunately, I cannot still understand your question. But I would like to try to understand it. When I could correctly understand it, I would like to think of a solution. I would be grateful if you can forgive my poor English skill.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/7ef3fe5e-577c-407f-b1d4-42262179c983n%40googlegroups.com.

[hbo one]

Hello Tanaike, 

Please dont be sorry. I really appreciate your time to help me out. 

Hope this helps :

Step 1: Lookup Column E

Step 2: Search Column C and find if there are any value matches from column E.

Step 3 : Highlight cell in column C and Column E if there is a Match. (it can be 1 match or it can be many, all duplicates should be highlighted)

Step 4: Skip if Cell is blank. ( Do not Highlight).

Hope this helps.

On Thu, Sep 22, 2022 at 11:23 PM Tanaike <kanshi...@gmail.com> wrote:

Thank you for replying. I would like to support you. But, I have to apologize for my poor English skill, again. Unfortunately, I cannot still understand your question. But I would like to try to understand it. When I could correctly understand it, I would like to think of a solution. I would be grateful if you can forgive my poor English skill.

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/4Ynv-0tbZ2Q/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.

[hbo one]

Thank you for your recommendation. But I am new to Scripting, I am not sure how to make the change you are recommending.

Here is the link for my Google Sheet, Can you be kind and help me to make this changes,

Link : https://docs.google.com/spreadsheets/d/1Y_QBvSIn5TIS3N-YOMKICFU67ZAeVOji9xKugu9k3as/edit#gid=0

Really appreciate your help.

Thank You

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAANZsGOzmt9uVt_QrMvVNY-N3ZpaZFGPnR%2BAzfBGw%2B6Dr4%2ByvQ%40mail.gmail.com.

[cbmserv...@gmail.com]

I updated the script to only highlight the cells in Column C or E if dups and to skip over blank cells.

 

Here is the code: (just paste it over what you have and save it). Then you can run it from the Custom menu.

 

/**

 * Finds duplicate rows in the active sheet and colors them red,

 but only pays attention to the indicated columns.

 */

 

function findDuplicates() {

  // List the columns you want to check by number (A = 1)

  var CHECK1 = 2; // Column C

  var CHECK2 = 4; // Column E

    

  // Get the active sheet and info about it

  var sourceSheet = SpreadsheetApp.getActiveSheet();

  var numRows = sourceSheet.getLastRow();  //return total row integer

  var numCols = sourceSheet.getLastColumn();  // return total column integer

 

  // Find duplicates in the FindDupes sheet and color them in the main sheet

  var data = sourceSheet.getDataRange().getValues();

 

  for (i = 1; i < data.length - 1; i++) 

  {

    for (j = i+1; j < data.length; j++) {

      if  (data[i][CHECK1] == "") {continue;}

      if  (data[i][CHECK1] == data[j][CHECK2]) {

         sourceSheet.getRange(i+1,CHECK1+1).setBackground("red");

         sourceSheet.getRange(j+1,CHECK2+1).setBackground("red");

        }

    }

  }

  

};

 

/**

 * Adds a custom menu to the active spreadsheet

 */

function onOpen() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet();

  var entries = [{

    name : "Find Duplicates",

    functionName : "findDuplicates"

  }];

  sheet.addMenu("Custom Menu", entries);

};

 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CACkFDNZDnycZgq%2B_MMviQJEB0KhuQMB5HaMAzEDAhUvjENsu1w%40mail.gmail.com.

[Tanaike]

Thank you for replying. Unfortunately, from your reply, I cannot still understand your expected result. By this, I cannot still think of a solution. I think that this is due to my poor English skill. I deeply apologize for my poor English skill again. But, now I noticed that an answer has already been posted. In this case, I would like to respect the existing answer.

[hbo one]

Hi George, 

thanks for the code. But for some reason when I am running script it is only highlighting one value. Please see screenshot, One that are in red circle are also duplicate but script is not highlighting.

Link : https://docs.google.com/spreadsheets/d/1tRYErNs9dP9D-GqB-UHviAjL7cWXmdt_mcGJvgyIjQU/edit#gid=0

[CBMServices Web]

Yeah there is a logic error in the code.

 Change this line to the following:

for (j = 1; j < data.length; j++) {

instead of:

for (j = i+1; j < data.length; j++) {

That should fix it.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/789e7c92-08c0-4f41-93d8-8cc2d245320bn%40googlegroups.com.

[hbo one]

Thank you George.

Works like a charm.