Find future On Hand Quantity for duplicate values

Hey guys,

Happy New Year!

I am trying to calculate "Remaining OH" qtys based on Total OH (On Hand) and Future Usage of parts that repeat themselves in a column for future dates. In the attached Sample Data, Column A has part numbers and some of them repeat themselves for future dates in column D (Sorted earliest to latest). Column B and C are Current OH and Future Requirement values respectively. Column E is where the calculation should happen where I am expecting the see how much I will have left after each part has been used on a specific date. Highlighted are the parts that repeat more than once in the table. For Ex: Part # 102 starts with an OH qty of 10 and 5 of which are being used on 2/15/2021 resulting in 5 as Remaining OH Qty. 8 of the same part# 102 are needed on 12/14/2021, hence the Remaining OH should be -3 (5-8). It repeats again third time in the table where 5 are required and the resulting Remaining OH Qty should be -8 (-3-5).

In my original file, I've had luck with solving the issue if the part# repeats only twice. Anything more than that does not return the expected value. I've manually entered the expected results in Column E. Feel free to add a column to enter formula and return expected values.

If the explanation is not clear, Please feel free to shoot your questions.

Thanks in advance

VJ

Hi Bo_Ry,

This is amazing. It worked. I just figured out a way using Index and match but your method is simple. If you don't mind, could you explain what Lookup(9^9) does? I'm new to this concept.

VJ