Searching for duplicate values within sets of 'n' rows within a range

Question

https://docs.google.com/spreadsheets/d/e/2PACX-1vTswXxlMFGFOYiG8jatnLTpN4hS3rhVouBfshRIMXTKi3wmpQuZ6MIabfjcqTXwqlILRcxOxr4o3xqc/pubhtml?gid=1249461985&single=true

I have the above spreadsheet set up to act as a daily schedule, in the range C5:I58. I have times in rows starting at 0800 and increasing in 30 min intervals e.g. 0830, 0900... until 1630 and then dates from today to today+6 as columns. Each time covers 3 rows and a value (retrieved via formula) can appear multiple times per day (i.e. per column) but in a maximum of 2 consecutive time periods i.e. twice within 6 consecutive rows.

What I would like to to is highlight the cells where the same value appears consecutively in the above case. I am aware that =COUNTIF(C5:C58,C5)>1 can be used to find duplicate values in a column but don't think it can be adapted to this situation specifically.

Therefor I expect I might have to create an iteration loop, but I wouldn't know where to start in this case! Any help greatly appreciated.

Answer 1

Based on: "Each time covers 3 rows and a value (retrieved via formula) can appear multiple times per day (i.e. per column)"

With condition: in "a maximum of 2 consecutive time periods i.e. twice within 6 consecutive rows" and to "highlight the cells where the same value appears consecutively in the above case"

Applying Conditional Formatting to the range C5:I58

Where the Format rule selection is Custom formula is, and uses the following formula:

=COUNTIF(INDIRECT(ADDRESS((QUOTIENT(ROW()-5,3)*3)+2,COLUMN())&":"&ADDRESS((QUOTIENT(ROW()-5,3)*3)+10,COLUMN())),INDIRECT(ADDRESS(ROW(),COLUMN())))>1

It will highlight the individual cells (not the block of 3 cells) where there is a duplicate within 2 consecutive time periods

Explanation:

COUNTIF(.....)>1 is when there are more than 1 (ie. a duplicate)

INDIRECT(.....) allows us to use the ADDRESS as a range of values

ADDRESS((QUOTIENT(ROW()-5,3)*3)+2,COLUMN()) is the first cell of the range (in conditional formatting) to be counted

ADDRESS((QUOTIENT(ROW()-5,3)*3)+10,COLUMN()) is the last cell of the range (in conditional formatting) to be counted

INDIRECT(ADDRESS(ROW(),COLUMN())) is the cell being compared

Answer 2

Highlight consecutive duplicates in column C

function consecdupes() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const vs = sh.getRange(2,3,sh.getLastRow() - 1).getValues().flat();
  vs.forEach((e, i, a) => {
    if (i > 0) {
      if (e == a[i - 1]) {
        sh.getRange(i + 1, 3,2).setBackgrounds([["#ffff00"],["#ffff00"]]);
      }
    }
  });
}