Forum Discussion
Conditional Formatting Duplicates in Two Columns
Hello,
I have a spreadsheet where I track calls my office receives. I have a table that includes the date and name for each call. Sometimes calls are entered in the spreadsheet multiple times. I would like to create a conditional formatting formula that will flag if a name is entered twice in one day. Is there a way to highlight cells that are duplicated in two rows of a table?
Thank you!
2 Replies
Does this example show what you want to achieve?
=COUNTIFS($D$3:$D$36,$D3,$E$3:$E$36,$E3)>1This is the rule for conditional formatting that i've used for the sample database.
My only comment is if your Date column is actually Date-Time then a minor tweak like the following is needed:
=COUNTIFS($D$3:$D$36,">="&INT($D3), $D$3:$D$36,"<"&INT($D3)+1, $E$3:$E$36,$E3)>1note: you can also replace $D$3:$D$36 with something like $D$3:.$D$999 where the period after the colon is a shortcut to the TRIMRANGE function to automatically find the end of the 'used' range so you can use this formula on columns of various length and/or continue to add rows and still have the formula work.
