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.
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
and cf would be:
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, ))