Forum Discussion
Highlight Cell if Duplicate Date in Other Column
I've been unable to find the right formula or method to go about this.
As an example below, I'm trying to highlight column B if has the same date in column A
So, A for 01/08/2023 should be highlighted as it appears twice for the same day
I do not want it highlighting the row or column A either
Also how do I write formulas from say $A2: the end of the column without having to use $A$1048576?
Please advise
Column A B
| 31/07/2023 | A |
| 31/07/2023 | B |
| 31/07/2023 | C |
| 01/08/2023 | A |
| 01/08/2023 | A |
| 01/08/2023 | A |
| 01/08/2023 | B |
To highlight you may use the rule
with formula
=COUNTIFS($A:$A, $A2, $B:$B, $B2 ) - 1To return the range
=$A$2:INDEX($A:$A, COUNTA($A:$A) )- The formula is causing Excel to run more slowly now with spinning blue circle / not responding.
I don't think it's highlighted the correct cells and highlighting everything blank in the column.
Please adviseYou may modify rule formula as
=( COUNTIFS($A:$A, $A2, $B:$B, $B2 ) - 1 )*($B2 <> "" )Conditional formatting in general is not the fastest operation. Hope you didn't apply the rule to entire column, take the range with some gap, i.e. first 100000 rows.
