Home > database >  Color Duplicates within that week
Color Duplicates within that week

Time:04-20

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

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

  • Related