Excel conditional formatting

Copper Contributor

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?

3 Replies

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....

 

Sample.png

 

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) = 1

 

IMG_2233.jpeg

 sorry 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. 

@L z. 

@Chellybean 

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))