CODEWITHSUNDEEP
arraysgoogle-sheetsduplicatesconditional-statementsmatch
djbeasley05
djbeasley05

Reputation: 1

Compare columns and rows for duplicates

Pool Reservations:

I am attempting to create a sheet for pool reservation. Column A dictates Time and Lap lane. I.e 5:15 AM 1 is for lap lane one. Column E is for the date. The time and lap lane can be scheduled for multiple days but the time/lap lane cannot be scheduled twice for the same day. I would like to highlight a row red if columns A and E within a row match A and E within a different row. In the example, both rows 3 and 6 should be highlighted red.

Upvotes: 0

Views: 142

Answers (1)

Raserhin
Raserhin

Reputation: 2676

So right now you have three columns and you want to check if there are any duplicates in that columns.

Something like this:

Initial sample

I made this simple script:

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getRange("A1:C8");
  const values = range.getValues();
  
  for(let i = 0; i <  values.length; i++){
    let row = values[i];
    for(let j=0; j < values.length; j++){
      // Check if there are any row (excluding the current one) with the same values
      if(j != i && JSON.stringify(row) === JSON.stringify(values[j])){
        sheet.getRange(i+1, 1, 1, row.length).setBackground("red");
      }
    } 
  }
}

Basically I would get all the rows inside the range variable and check for every single one of them to see if there are any row repeating. I think the code itself is very self explanatory the only thing I think is worth mentioning is comparing the arrays. In javascript comparing the array would be a reference comparison, read more about it in this question.

The final result after executing the code is:

Final sample

Upvotes: 1

Related Questions