Search code examples
arraysgoogle-sheetsduplicatesconditional-formattingcountif

Color Duplicates within that week


How do I automatically color duplicates in a column "within that week" while I am filling records in the sheet.

For Eg: All new records within the week are colored Green and duplicates within that week is colored Orange.

Week wise Duplicates

I have been trying Format > Conditional Formatting however I am not sure what to enter in the Custom Formula to limit the search and highlight week wise.

Any help is appreciated. Here's a sample sheet and expected results

Week wise Duplicates


Solution

  • if Monday is the 1st day of the week and Col1 & Col2 are treated together:

    =ARRAYFORMULA(IF(COUNTIFS(
     IF(B2:C="",, WEEKNUM(VLOOKUP(ROW(A2:A), IF(A2:A<>"", 
     {ROW(A2:A), A2:A}), 2, 1), 2)&" "&B2:C), 
     IF(B2:C="",, WEEKNUM(VLOOKUP(ROW(A2:A), IF(A2:A<>"", 
     {ROW(A2:A), A2:A}), 2, 1), 2)&" "&B2:C), 
     SEQUENCE(ROWS(A2:A), 2), "<="&SEQUENCE(ROWS(A2:A), 2))>1, 1, ))
    

    enter image description here


    and cf would be:

    enter image description here


    update

    if Monday is the 1st day of the week and Col1 & Col2 are NOT treated together:

    =ARRAYFORMULA(IF({COUNTIFS(
     IF(B2:B="",, WEEKNUM(VLOOKUP(ROW(A2:A), IF(A2:A<>"", 
     {ROW(A2:A), A2:A}), 2, 1), 2)&" "&B2:B), 
     IF(B2:B="",, WEEKNUM(VLOOKUP(ROW(A2:A), IF(A2:A<>"", 
     {ROW(A2:A), A2:A}), 2, 1), 2)&" "&B2:B), 
     ROW(A2:A), "<="&ROW(A2:A))>1, COUNTIFS(
     IF(C2:C="",, WEEKNUM(VLOOKUP(ROW(A2:A), IF(A2:A<>"", 
     {ROW(A2:A), A2:A}), 2, 1), 2)&" "&C2:C), 
     IF(C2:C="",, WEEKNUM(VLOOKUP(ROW(A2:A), IF(A2:A<>"", 
     {ROW(A2:A), A2:A}), 2, 1), 2)&" "&C2:C), 
     ROW(A2:A), "<="&ROW(A2:A))>1}, 1, ))
    

    enter image description here