Shelf Life problem - check customer shelf life requirements with shelf life on inventory

Dear Friend,

My name is Mads. I have faced an issue which i have spent hours trying to solve, however, without luck so far. I would appreciate any help on this.

The problem is to identify if there is sufficient inventory to satisfy customer orders with respect to the remaining shelfs life per. material (multiple batches) and customer shelf life requirements per material.

Data is found in attachment.
Sheet 1 (Inventory data): Explains products, quantity in stock and remaining shelf life per. material. (please notice one product may have multiple batches with different shelf life).
Sheet 2 (Customer reg. shelf life): Explains customer shelf life requirement per material. (At loading, remaining shelf life on inventory must be higher than customer requirement shelf life)
Sheet 3 (Order to customer): Explains material purchase per. customer and quantity and loading date.


So far, i have only been able to check if at least one batch of each product has greater remaining shelf life than required per customer. However, i do not know if there is enough inventory to satisfy the customer order. I would like an overview explaining if each order can be fulfilled in full quantity without violating customer requirement shelf life.

FYI: inventory data is pulled 3 months ago, hence many products will be too old.

I really appreciate if you could recommend a solution or help me along the way. The uploaded data is intended to be clean without my own suggestions to minimize confusion.

Please let me know if you need any additional information.

Thanks you very much

Best regards
Mads