Compare columns and rows for duplicates

Question

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.

Why is the reason for not putting the lane number in a column of its own? The way you mixed those two pieces of information in the same column makes working with them significantly harder and confusing to readers. I would strongly suggest to put lane in a separate column. Also, would you be accepting a [Apps Script](https://developers.google.com/apps-script) solution for this problem? — Raserhin, Jun 10 '20 at 07:54
Definitely a great suggestion on putting the lane in a second column. I'll be honest, this is extremely new for me and so I am not certain how to use Apps Script. — djbeasley05, Jun 10 '20 at 22:27
I think this would be extremely difficult to do only with formulas, Apps Script would be the way to go here — Raserhin, Jul 02 '20 at 10:00

score 1 · Answer 1 · answered Jul 13 '20 at 07:46

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

Something like this:

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:

Compare columns and rows for duplicates

1 Answers1