Sales projection spreadsheet: redistribution of sales per month - Complex

Dear all,
I would appreciate everyones help on the following issue I try to solve:

Problem: I set up a projection model to simulate the sale of 36 items over 12 months i.e. each month three items. This model was just for projection purposes. Now I have startet actual sales and would like to update the model for the actual sales I realized until month 3 for instance. While I might have sold already items in an earlier month that were projected for a later month, I still want the model to automatically redistribute all unsold units such that each month three units are sold.
An Example: until month three I have sold:
- 2 items in month 1 that were projected for month 5,
- 2 items in month 2 that were projected for month 2
- 1 item in month 1 that was projected for month 1
Thus I have already sold items scheduled for month 5 and have not sold items scheduled for month 1 and 2 and 3. However I need the model to show still three sales per month meaning that the unrealized sales in months 1, 2 and 3 are not shifted to a later date.
Because I use time-sensitive Return Metrics I need to still present three sales per month. In the real model I have more than 4 years of sales. Thus automatically updating would be very helpful.
The model looks like follows:
Column A: number of month (1-12)
Column B: 36 different items (i1-i36) which I want to sell within one year i.e. 3 per months.
Column C shows the price for each item (x1)
Column D shows the month the item was actually sold

A B C D
1 i1 X1 1
1 i2 X2
1 i3 X3
2 i4 X4
2 i5 X5 2
2 i6 X6 2
3 i7 X7
3 i8 X8
3 i9 X9
4 i10 X10
4 i11 X11
4 i12 X12
5 i13 X13 1
5 i14 X14 1
5 i15 X15


I would appreciate any help or ideas how to solve this problem. I have thought about iterative calculations but I just cannot find a solution.
Thanks everyone for your time!

Is there noone who can think of a solution?

Hi Bobo,

hope you are well,

how you want to do this? by formulas or using vba/Macros?

Maybe it would be easier to receive help if you could attach a file as example, as well inserting the code you are working on,

for the code you should insert between the quotes "[ CODE ][ /CODE ]" they will appear when you click on the # above and pasting your code in between

if you donīt know how to do that see the images below

Hope that can help you get some help

attach.jpg
addingcode.jpg

lauren