Need urgent help please with if formula with multiple criteria

Hi Guys,

First post on this forum. Great to see so much help out there to enable us to better ourselves. Certainly want to get into Excel more and more, love what it can do.

I am trying to come up with an if formula that will return the following;

Where the consignment note value in column D is the same, (duplicate) and the date in column B is different i need to return the word, "duplicate" and where this criteria is not met return the word, "No". Although the consignment note may be the same, (duplicate) in column D, the catch is that it can only be a duplicate on a different date in column B.

The formula i used was;

=IF(AND(COUNTIF(B$5:$B40,B40)<>B39,COUNTIF(D$5:$D40,D40)>1),"DUPLICATE","NO")

I know it is wrong!!

It is returning the word, "duplicate" for the same consignment (column D) on the same date (column B) as well as different date (column B). I only want it to return, "duplicate" when the date (column B) is not the same.

It also needs to return the word, "duplicate" very each same consignment after the first consignment (column D) on a different date (column B).

See attached file with comments in F39 & F41.

Please help!

Adam