HOW TO IDENTIFY THE FIRST SAILING FOR A GIVEN SHIPMENT?
Case explanation
One shipment can be split and shipped at different times on different vessels. Each sailing is represented by a delivery. Among all deliveries (one delivery = one line of my file) from the same shipment, which one has the earliest departure date?
Calculation Steps
1) You need a unique shipment identifier. The batch is serving that purpose. STO cannot be used as you may have a PO creation without STO created yet.
2) Let’s identify the smallest non-zero value from a range. (“non-zero” because I have deliveries that have not sailed yet, their Bill of Lading date is of course 0 what will unfortunately be the value returned by a simple MIN function). To counter this issue I found an intelligent function on the web. It is a combination of the SMALL and COUNTIF formulas as shown below:
=SMALL(A1:A100,COUNTIF($A$1:$A$100,0)+1)
SMALL returns the k-th smallest value in a data set. Where the countif is counting the zeros in the range and adding one and is then used to tell SMALL to return the k-th smallest value.
You need to include that condition because the number of zero values can vary in your sample, and you need to set the k accordingly. Yes, if you have a sample {2,4,3,4,5} : 0 zero => smallest non-zero value is the 1-st smallest (0+1). However if you have {4,0,8,0,5}: 2 zeros => smallest non-zero value is the 3-rd one (2+1) and this is 5.
This formula is perfectly working on a fixed range. The issue is that I don’t want to return the earliest BL date among all B/Ls (my full column) but I want to consider only the values linked to the same shipment (batch).
3) In order to consider a sub-sample we need to include an IF array function.
Note: enter “ctrl+shift+enter” combination to validate an array formula
= IF($AB$2:$AB$3852=AB2,$AO$2:$AO$3852)
The formula returns an array containing all B/L dates ($AO$2:$AO$3852) provided they have the same batch number (AB2). This is the range we need to consider in our SMALL function instead of a fixed range like A1:A100.
4) The combination would look like below and return the smallest non-zero value (earliest B.L date) for a shipment based on its batch number:
=SMALL(IF($AB$2:$AB$3852=AB2,$AO$2:$AO$3852),
COUNTIF(IF($AB$2:$AB$3852=AB2,$AO$2:$AO$3852),0)+1)
Unfortunately this is not working! Because COUNTIF cannot be used with an array input!!!
5) However COUNTIF is equivalent to a SUM IF combination:
=SUM(IF(A1:A100=”a”),1,0) returns the same as =COUNTIF(A1:A100,”a”)
6) Let’s do this change in our formula, remembering that we have the variable range. It will look like this:
=SMALL(IF($AB$2:$AB$3852=AB2,$AO$2:$AO$3852),
SUM(IF(IF($AB$2:$AB$3852=AB2,$AO$2:$AO$3852)=0,1,0))+1)
This is our final formula.
7) Copy paste it in the first cell of a new column. And then validate it as an array by typing ctrl+shift+enter. I should look like this:
{=SMALL(IF($AB$2:$AB$3852=AB2,$AO$2:$AO$3852),
SUM(IF(IF($AB$2:$AB$3852=AB2,$AO$2:$AO$3852)=0,1,0))+1)}
#NUM! is ok, it appears when there is no B/L date to be returned.
8) Only after you have done that, double click to extend the formula to all cells of the column. To be sure you extended the right formula, check if the variable cell is changing according to the line number.
For any delivery (line), it will display the earliest B/L date for the given shipment. Meaning that all deliveries with the same batch should show the same result, isn’t it?
9) Last step, create a new column to identify for any delivery if it is the first one! This is a simple comparison between the earliest B/L date we just identified with our magnificient formula and the actual B/L date of that delivery.
=IF(AO2=0,"no bl date",IF(AO2=CJ2,"1st sailing","-"))
Here I also eliminated the case where we have no B/L dates entered because otherwise the test results in an error.
As a result we have categorized any delivery into 3 categories:
- “no BL date” (has not sailed yet)
- “1st sailing”
- “-“ (sailed but is not the first one)
ILLUSTRATION
Articles
