# First date that matches criteria

Posted on

QUESTION :

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