Allocate Values from a table into columns under corresponding Months

Hello

I have a table with Unit Numbers in Column A (24A, 24B, 25A, 25B and so on) , Sale Month in Column B, Price in Column C and Stagewise Price Breakup (10 Stages) in Columns D to M.

Price Breakup is pre-defined (Like monthly installments) based on certain percentage applied to each stage and that payment becomes due when that particular stage is completed. These stagewise payments are not equal.

Time frame for each stage is also pre-defined i.e. if "Stage1" i.e. "Sale" happens in "Month1", then stage2 comes in Month2, Stage3 comes in Month4 and so on. There are 2 month gaps between certain stages. Once the Sale of specific Unit is made, then a specific month (For example Mar-17) will be applied as start month (For stage1) for that unit.

I have another sheet which looks like a running calender, which has Unit Numbers in Column A (same as the previous Data sheet) and Column B onwards have Months in the header row i.e. Feb-17, Mar-17, Apr-17 and so on till year 2020.

I am trying to figure out a way to automatically allocate values in the Data table to the Calender under specific months, in the row for the specific Unit No.

For example, if Unit 25B is sold in Mar-17, then I will enter "start Month" or "Stage1" as Mar-17 and the following stagewise Values (which are already pre-defined in the table) should be allocated in the calendar under correct month i.e. Stage2 value should fall under Apr-17, Stage3 Value should fall under Jun-17 and so on.

Can someone help please ! I would really appreciate it !

Thanks

Hi
Try this approach
In CashFlow sheet insert 2 columns B and C
In CashFlow!B4 use the following formula and copy down

Formula:

=IFERROR(MATCH(A4,PS!$A$4:$A$25,0),"")

In CashFlow!C4 use the following formula and copy down

Formula:

=IF($B4<>"",MATCH(INDEX(PS!$B$4:$B$25,$B4),Dates,0)+COLUMNS($A$3:$C$3),"")

In CashFlow!D4 use the following formula and copy down and forward

Formula:

=IFERROR(IF($C4>COLUMN(D4),"",IF($C4=COLUMN(D4),INDEX(PS!$E$4:$N$25,$B4,1),IF($C4=COLUMN(D4)-1,INDEX(PS!$E$4:$N$25,$B4,2),

IF(MOD(COLUMN(D4)-$C4,2)=1,INDEX(PS!$E$4:$N$25,$B4,INT((COLUMN(D4)-$C4)/2)+2),"")))),"")

See the file

Hi Jose....thanks for this. It is working fine. If I want to play with the gap between 2 stages, then how should I do it? I mean at the moment, after first 2 stages, it has following values placed under alternate months in cashflow. I would like to be able to adjust the gap between each stage (which will be same all the units). Could you please explain where I can change to adjust the gaps between the stages? thanks a lot

Hi
For gaps =3 use in D4

Formula:

=IFERROR(IF($C4>COLUMN(D4),"",IF($C4=COLUMN(D4),INDEX(PS!$E$4:$N$25,$B4,1),IF($C4=COLUMN(D4)-1,INDEX(PS!$E$4:$N$25,$B4,2),

IF(MOD(COLUMN(D4)-$C4,3)=1,INDEX(PS!$E$4:$N$25,$B4,INT((COLUMN(D4)-$C4)/3)+2),"")))),"")

You can use a cell to set the value of gap for all, or a new column to set the gap for each unit.

Hello Jose.....Thanks for this. Unfortunately, the between all stages is not the same. For example, the gap between Stage2 & stage3 is 2 Months. All other stages are only 1 month apart. Also, the last two stages i.e. Stage9 and Stage10 fall in the same month, so the total of stage9 & Stage10 should reflect under the same month.

Is there a way, I can customise the gap between individual stages for individual units which may not be evenly distributed between all 10 stages?

For example, i mean For Unit 24A, Stage1>Stage2 could be 1 Month, Stage2>Stage3 could be 2 Months, Stage3>Stage4 could be 1 Month and So on.
For Unit 37A, Stage1>Stage2 could be 2 Months, Stage2>Stage2 could be 2 Months, Stage3>Stage4 could be 4 Months, Stage4>Stage5 could be 1 Month and so on.

I would really appreciate if you could show me how and where I should add columns for adjust stage gap for individual units and individual stages. So effectively, I can customise the timeline for each unit and also customise the gap between the stage depending upon what unit it is and when it starts.

Thanks again for your help !

Hi
I suppose you want something like this
Insert a new column D and define a rule for the Unit, for example R_01.

The rules can be defined in a new sheet by code an values like {R_01\1\2\4\6\8\10\12\14\16\18}

Use in E4 the following formula and copy down and forward

Formula:

=IFERROR(INDEX(PS!$E3:$N3,MATCH(COLUMN(E$3)-$C4+1,INDEX(GAPS!$B$3:$K$22,MATCH($D4,GAPS!$A$3:$A$22,0),0),0)),"")

Note: Formula in C4 are adjusted to accommodate the new column so use this formula and copy down

Formula:

 =IF($B4<>"",MATCH(INDEX(PS!$B$4:$B$25,$B4),Dates,0)+COLUMNS($A$3:$D$3),"")

See the file

Thanks a ton mate ! It works like a charm! and very easy to control and customise. Thank you very much for all your help !

I am happy to have helped and thanks for the feedback.

Please do not forget to mark this thread as SOLVED.

Hello Jose....... While replicating this formula you gave me a few days back, I have encountered a practical problem in a few exceptional cases.

In case of a few units, depending upon the sale month, I need the values for number of stages to fall under the same month i.e. for example the values/amounts allocated Stage1, stage2, Stage3 should all come under Month1 ....
Is there a way, I could display the TOTAL of multiple stages under the same month using a formula?

I tried putting the same month number i.e. "1" under multiple stages in the sheet "Gaps", but it only picks up Value for one stage and doesn't display total. It would be easier if the formula could just pick up and total stage numbers based on the Gaps table, as I can customise it to suit very easily.

I would really appreciate your help.

Thanks again