First date that matches criteria

I have 2 columns, LotNbr and ShipDate. I’m looking to create a column that contains whether the ShipDate was the first date for that LotNbr:

LotNbr | ShipDate | FirstShipment
aaaan1 | 1/1/2016 | yes
aabaa2 | 1/1/2016 | yes
aaaan1 | 2/1/2016 | no

I had =IF(MIN(IF(A2=$A$2:$A$9999,$b$2:$b$9999))=b2,"yes","no")

but there’s a problem if the same lot shipped multiple times the same day:


LotNbr | ShipDate | FirstShipment
aaaan1 | 1/1/2016 | yes
aabaa2 | 1/1/2016 | no [should be yes]
aabaa2 | 1/1/2016 | no [should be no]
aaaan1 | 2/1/2016 | no
caaaa1 | 3/1/2016 | no [should be yes]


assuming that your records are ordered by ShipDate, this function should work:

(1st record on 2nd rows) -> on cell C2 (FirstShipment)

=IF(COUNTIF($A$1:$A1,A2)=0,"yes","no")

copy the formula down.

The COUNTIF function checks the upper range of the corresponding rows, if the LotNbr is already mentioned.

e.g. on the 3rd record (4th row):

C4 = IF(COUNTIF($A$1:$A3,A4)=0,"yes","no")