I am building a "Routing Check" for the ground shipment. please see the example 1 below.

• Group by the "HB"

• We have a shipment from AMS to BUH.

• if the GNR Origin = Consol Lane left 3 letter, it will be a "First leg".

• if the GNR Dest = Consol lane Right 3 letter, it will be the "last leg".

• the rest will be "transit Leg"

The aim/goal for this little project is want to flag the "HB" that is missing the "Last leg"

So, the Example 1 will be fine

please see below Example 2

• we have the "First leg" from AMS to FRA, and the "Transit Leg" from FRA to BUD
• but we are missing the "Last Leg" BUG to BUD
• i will to show it in the new column saying "Missing last leg" something like that(Example 3)

Example 1

Example 2

Example 3(Goal)

CodePudding user response：

You can use a formula like =IF(COUNTIFS(A:A,A2,B:B,"Last Leg")=0,"Missing Last Leg","OK") where Column A is HB Column and Column B is the Leg Check Column.

The COUNTIFS function will check the combination of the criteria of whether your HB and Last Leg Occur for an HB.