Forum Discussion
Excel conditional formatting
Hi all!
Im having a tough time figuring this one out. I usually use conditional formatting to find unique entries between two columns, however I now need them to be date specific. I want it to flag if it is unique per day.
I am reconciling between what we have on the books and what our statement says and there are some common charges that I don’t want being considered duplicate unless there was a charge for that on the same date as our records indicate.
thoughts?
Hi Chellybean
For this kind of things sharing a sample with the expected result (as recommended in Welcome to your Excel discussion space!) is always a good idea. Assuming I understood....
With data formatted as Table named Table1 + 2 defined names (in Name Manager):
Date: =Table1[Date]
Charge: =Table1[Charge]
Cond. Format. rule using formula that Applies To (with the above setup) =$C$5:$C$14:
=COUNTIFS(Date,$B5, Charge,$C5) = 1sorry for the confusion! It’s my first time posting. Above the duplicate cells are highlighted based on the dates. Even though there are other duplicate values i dont want them to highlight because they didnt occur on the same date.
For conditional formatting: Simply combine the search terms with each other!
Column B: =ISNUMBER(XMATCH(A2&B2,C$2:C$7&D$2:D$7)) Column D: =ISNUMBER(XMATCH(C2&D2,A$2:A$7&B$2:B$7))
