Hello,
I have the following Problem:
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:
Date | Component | Demand per day
12.01.2020 | 123 | 80
13.01.2020 | 123 | 80
14.01.2020 | 123 | 80
15.01.2020 | 123 | 80
16.01.2020 | 123 | 80
I don't really want to use a loop for the solution due to performance reasons.
Thank you for your help!
