Help with formula for a DIFOT report.

Hi,
I need to create a DIFOT report for my business.
I have a selection of sales that have multiple sales on each sales Order.
Some have dispatched on time and some have not.
I can get a report that report by Sales line if the orders have left on time but I need it reported based upon the complete sales order.
For example if all lines on the sales order have dispatched on time then they are a "Pass". If everything misses the request date they are "Fail". If the order has a mix on a Sales Order of Passes and Fails then the order would be deemed to be a "Fail".

I think some form of formula that looks at the range of cells in "OrderNo" and if the OrderNo are the same then look at the values in Order Line DIFOT Status and produce a result based upon the 3 options above.

Any help would be appreciatted.

CustomerName OrderNo Required ProductDescription Order Date DESPATCH Date Order Line DIFOT Status
CUSTOMER 1 112233 8/01/2021 Product 1 05-Jan-21 22-Jan-21 FAIL
CUSTOMER 1 112233 8/01/2021 Product 10 05-Jan-21 22-Jan-21 FAIL
CUSTOMER 1 112233 8/01/2021 Product 11 05-Jan-21 22-Jan-21 FAIL
CUSTOMER 1 112233 8/01/2021 Product 2 05-Jan-21 22-Jan-21 FAIL
CUSTOMER 1 112233 8/01/2021 Product 5 05-Jan-21 22-Jan-21 FAIL
CUSTOMER 1 112233 8/01/2021 Product 6 05-Jan-21 22- Jan-21 FAIL
CUSTOMER 1 112233 8/01/2021 Product 7 05-Jan-21 22 -Jan-21 FAIL
CUSTOMER 1 112233 8/01/2021 Product 8 05-Jan-21 22-Jan-21 FAIL
CUSTOMER 1 112233 8/01/2021 Product 9 05-Jan-21 22-Jan-21 FAIL
Customer 2 445566 8/01/2021 Product 12 05-Jan-21 08-Jan-21 PASS
Customer 2 445566 8/01/2021 Product 13 05-Jan-21 08-Jan-21 PASS
Customer 2 445566 8/01/2021 Product 14 05-Jan-21 08-Jan-21 PASS
Customer 2 445566 8/01/2021 Product 17 05-Jan-21 08-Jan-21 PASS
Customer 2 445566 8/01/2021 Product 18 05-Jan-21 15-Jan-21 FAIL
Customer 2 445566 8/01/2021 Product 19 05-Jan-21 08-Jan-21 PASS
Customer 2 445566 8/01/2021 Product 21 05-Jan-21 15-Jan-21 FAIL
Customer 2 445566 8/01/2021 Product 3 05-Jan-21 07-Jan-21 PASS
Customer 3 778899 8/01/2021 Product 15 05-Jan-21 07-Jan-21 PASS
Customer 3 778899 8/01/2021 Product 16 05-Jan-21 07-Jan-21 PASS
Customer 3 778899 8/01/2021 Product 16 05-Jan-21 07-Jan-21 PASS
Customer 3 778899 8/01/2021 Product 20 05-Jan-21 08-Jan-21 PASS
Customer 3 778899 8/01/2021 Product 20 05-Jan-21 08-Jan-21 PASS
Customer 3 778899 8/01/2021 Product 4 05-Jan-21 07-Jan-21 PASS
Customer 3 778899 8/01/2021 Product 4 05-Jan-21 07-Jan-21 PASS

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

One way to accomplish this would be to use J2:J26 as a helper column which could be moved and/or hidden for aesthetic purposes.
Column K could then be populated using:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

@GTNeil1, You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.