Providing an Availability Date

Hi,

I have a task to provide the availability date for confirming Customer Orders.

I have built an initial excel table with all the Demand (Customer Sales Orders) and Supply (Purchase Orders), then summarised it in a pivot table, with a running total (cumulative) based on a KEY (which is a concatenation of Delivery Date and Customer Sales Order (or blank if its a Purchase Order)).  Due to the concatentation, the date has converted to its numerical value (ie 8th Nov 2021 = 44508), but still allows the KEY to remain in date order.

The premise is, whenever the Grand Total (column G) reaches 0, then I have enough Supply to meet Demand.  When this is true, the preceeding rows where the Product Number is the same and the Grand Total is not equal to 0, the Availability Date would be the date from the line the Grand Total = 0.  As there may be several moments when the Grand Total = 0 for the same Product Number, this routine would need to be repeated, BUT restart when the Product Number changes.

To summarise what I want to achieve:

• while the Grand Total (column G) <> 0 and the Product Number remains the same, return the date of the next instance when the Grand Total = 0
• when the Product Number changes, then the calculation resets and starts again (as above, searching for next instance when the Grand Total = 0)

In the attached file, columns C:G in Sheet1 are the result of the pivot table, and I want to insert in column H the "date" when the next instance in column G = 0, then repeat and continue down the column.

A couple of notes:

• this insertion in column H is not required to be part of the pivot table.
• the data in the file is a snapshot of what I have, but gives a variety of examples:
  • only 1 instance when the Grand Total = 0
  • more than 1 instamce when the Grand Total = 0
  • instances when the Grand Total is > 0 for many rows (important to my nested IF statements)
  • instances when the Grand Total is < 0 for many rows (important to my nested IF statements)
• the total number of Product Numbers I am working with is closer to 6000 (why I want to build a more robuist solution)
• the cell colourings are only to group the Product Numbers and help to visualy

I have built a very clumsy nested IF statement (can be seen in column I), but due to the random number of rows containg the same Product Number, then the statement becomes very large, my exel performance slows down, and its very difficult to debug (for example, I need to modify the statement if I happen to find a Product Number that has an additional delivery date).

I have spent some hours to look at VBA "Do / While Loops" (and "Do / Until Loops") as I believe these may be a more elegant and robust solution, but my lack of experience here means I have made no headway at all; but I am very willing to learn.

I have found it very difficult to explain what I am looking to achieve, albeit the result seems quite simple.  I really hope that the file plus my explanation is initially sufficient to get someone interested to help.

Many thanks,

Martin.

System Overview

Windows 10 Enterprise

Version 21H1

OS Build: 19043.1237

Excel (64 bit)

MS365 Apps for Enterprise

Version 2102

Build: 13801.20960 Click to Run