I have a source document, which contains the production plan for the next months (component | date | demand) - see file attached. Now I have to calculate the demand per day for our suppliers. They are able to produce about 80 pieces a day.
The result should be a table, with the columns component, day, demand_per_day. Therefore I have a calendar with all working days of the specific supplier.
The demand_per_day should always be as low as possible. Example: Date | Component | Demand 15.01.2020 | 123 | 200
Expected result: Date | Component | Demand per day 13.01.2020 | 123 | 40 14.01.2020 | 123 | 80 15.01.2020 | 123 | 80
I saved this problem by using the intervalmatch function. The problem is, that sometimes the intervals are overlapping (e.g. if there is a demand of 200 pieces also on the 16th of January). Then the demand per day is over 80 pieces. The expected result in this case should be:
