Identifying discrepancies between two columns of data

[TiGar]

Good morning,
I'm back, with the latest formula I've got stuck with.
I'm trying to compare two columns, C Batch and D Expiry Date, which should be consistent.
In the attached, I have scanned the entries and can see some inconsistencies. For example, lines 592 and 593 are both Batch 'SE30' but have different Expiry Dates (30/09/2021 and 31/01/2022).
I am hoping to find a formula that will highlight these. I've been looking at MATCH formulas and conditional formatting rules, but haven't managed to come up with one that does what I need.
Can anyone here advise?
Many thanks, Tim

[RaulSerg]

Something like this will indicate the entire batch where something is missing:

Column F: =COUNTIFS([Batch],[@Batch])-COUNTIFS([Batch],[@Batch],[Expiry Date],[@[Expiry Date]])

It will give you values of 22 and 4 basically telling that for SE30 2 different dates are provided, of in total 26 occurrences, 22 have the same date and 4 have a different date.

[Richard Buttrey]

One way is a conditional format in F2

=IF(OR(C2<>C1,C2<>C3),TRUE,AND(C2=C3,D2=D3,C2=C1,D2=D1))=FALSE

It assumes the table is sorted by Batch and Expiry Date

[TiGar]

Hi RaulSerg,
This is genius. It would have taken me a long time to reach this point on my own.
One question I do have is, the 4 that have a different date sows a value of 22, and the 22 that have the same date, a value of 4. Is there any way of flipping this around?
Thanks again, Tim

[RaulSerg]

Can't think of anything more elegant: =IF(COUNTIFS([Batch],[@Batch])-COUNTIFS([Batch],[@Batch],[Expiry Date],[@[Expiry Date]]),COUNTIFS([Batch],[@Batch],[Expiry Date],[@[Expiry Date]]),)

[TiGar]

Elegance is sometimes overrated. Thank you!