Allocation of Quantity month & below

[Etax]

Hi,

Let me describe what I am expecting.

I have removed all other sheet that was earlier making confusions and trying to make more simple.
My ultimate goal is to get the answer in AN column which I have highlighted with green.

Data in AT:AY is simple the pulling the data from other sheet.

The main calculation column required is from AZ columns and to the right side.I have made for 3 months only with manual calculation in Month-1 i.e AZ:BZ column

I have show in AT Column Itemwise with their Sub Total at each.Here,I have taken of Month-1 and done calculation in AZ column whereas BA column is simply AY*AZ.


As I have multiple items with multiple month and data will accordingly shift to right side which I have started from AZ column as manual allocation is not possible in AZ column,so I do expect by vba that allocation of AM column based on month and item be by matching month AU column and item AT and the result towards column AZ & to right.

I hope now this would be clear.

Enclosed in attachment file.If any queries then kindly let me know.

[Etax]

If more detailed information required then plz let me know.

[Etax]

Hi,

All of you I have revised my file and sticked to single sheet .I have removed # Post 1 entire description and revised it accordingly making it more simple to understand.
I have also changed the heading i.e Title to more appropriate one.

[Etax]

I have posted in another forum at https://www.mrexcel.com/board/thread...below.1237389/

Hoping answer to get in this .

[JeteMc]

Perhaps the following formula based proposal will help:
1. For AM4 and down:

Please Login or Register  to view this content.

2. For AN4 and down:

Please Login or Register  to view this content.

Let us know if you have any questions.

[Etax]

Thanx JeteMc,
It's not that I am expecting.Refer AZ column,this column data I require which is very much important.
Breakup of AM4 data which is 50.

Similarly breakup of AM5 data which is 213 shown in AZ below Product A.
Because without getting data in AZ column,I cannot get result in BA and AN column.Hence,AZ calculation is very much important.
I don't think with formula it is possible and is possible by VBA only.

Here,also I have presented data of Month 1 only in B:AN column.There will be of course month 2,Month 3,etc.also.whose result shall be in BB,BD and the like.

[JeteMc]

This yields the expected values in column AZ:

Please Login or Register  to view this content.

The formulas in columns BA and AN are not changed.
Let us know if you have any questions.

[Etax]

Thanx for the response and giving your valuable time to my post.

For AZ column it works very fine.But the formula should be little dynamic as when I have added data in in B column B6,B7,B8,B9 and the copying your formula towards BB and BD columns,it displays the incorrect result which I have highlighted with red in a separate sheet.Also in another sheet, correct result I have mentioned manually at BB and BD columns.
I think I hope that you have understood the whole issue.

Also,M column decided in which month in row 2 from AZ towards right data should be shown.

[JeteMc]

This might help.
As modeled on Sheet1:
1. H5:M17 reverse the row order of the extracted columns using: =INDEX(A$5:A$17,AGGREGATE(14,6,(ROW(A$5:A$17)-ROW(A$4)),ROWS(H$5:H5)))
2. O5:T17 display the quantity per item and month using: =IF($I5>O$2,"",IF($H5=O$1,MIN($K5,O$3-SUM(O$4:O4)),""))
3. O19: T19 display the total cost using: =SUMPRODUCT(O5:O17,$M5:$M17)
Let us know if you have any questions.

[Etax]

Thanx for the reply.

You have understood the whole story.As per your #Post 9,Answers seems to be correct.But the model is changed.
Is it possible without change in the structure and same things be achieved.Month in row2 from AZ i.e Month-1,Month-2 can be replaced by 1,2 and so on.

AT4 and AT5 blanks can be reduced by change in the formula .

I have here for demo shown only two product but in fact there are 8-10.And month reflected only 3 but infact there is 12 month.

Since,I am operating in Office 2021,available dynamic array formula would be an added advantages get to learn.

Hoping your positive response.

[JeteMc]

I am using the 2019 version of Excel so I can't use dynamic array formulas.
Perhaps someone else will be able to help.
I hope that you have a blessed day.

[Etax]

Can't you do in the earlier format rather than reversing it and using New Format in Sheet1.#Post 8 attachment format I want to stick to it due some reasons.

Your #Post 7 result was fine.Simply necessary changes has to be done removing error where wrong data is displayed without changing the structure of data.

I hope u understand what I am trying to say.

[JeteMc]

The following array entered formula will fill the results into columns AZ, BB and BD on the Destination-WrongResult(Month2):

Please Login or Register  to view this content.

Note that for the 2019 and earlier versions this requires array entry (simultaneously press the Ctrl, Shift and Enter keys).
Note that the above formula uses the calculations from Sheet1.
Note that on Sheet1 A5: F20 are filled using assignments: ='Destination-WrongResult(Month2)'!AT4
Let us know if you have any questions.

[Etax]

I have seen the file. But this will not work as it would lead to additional helper sheet to be depend upon which is otherwise could have been in a single sheet.

Thanx for your consistent interest and giving your valuable time to my post .

I hope some other will help me in this matter.

[JeteMc]

Moved the helper section to the Destination-WrongResult(Month2) sheet in the range BN4:CJ25, which may be expanded as needed.
Cells BU4:CJ6 are filled using: =INDEX($B4:$B20,COLUMNS($BU4:BU4))
Let us know if you have any questions.

[Etax]

Thanx JeteMc

Heartly respect you for your contribution to my post.

Since,there is no linking between B,M and AM which is to be straight forward.This means that I cannot achieve the result as per mine standard format,I have to go with your # Post 9 Sheet1 Model.

That (#Post 9) seems to better than #Post 15.

I am marking this as solved for JeteMc contribution.But would be open for everyone who could provide us the result without any helper columns/worksheet in a straight forward manner as stated above.

[jindon]

See if this is calculating correctly.

Please Login or Register  to view this content.

[Etax]

Thanx Jindon

For you kind interest and stepping in.

No,not that I am trying to achieve. You have to build data in columns from AZ4 onwards.After getting data in those columns then only AN columns answer is to be received which is simply the summary.(Note: Also,answers are coming wrong on alteration of value from B:AM)

The main problem is to get breakdown of AM column data in the manner presented in AZ,BB,BD and so on.

So my expected results is to get data in columns AZ to Right uptill BW (now increased)i.e getting data in those tabular. This is the main problem in getting.( I have highlighted with sky blue color).This highlighted part is very very important.

If AZ & to right data if got then ultimate result in AN column can be achieved easily.

I have presented only here for 3 month as demo. In fact there is of 12 month which will be updated in due course of time.

Conditions: Data from B to AN will gradually increase to downwards as well as AT to AY also hence flexibility must exists in code.

Note: Plz focus on getting data automatically at AZ columns and to Right i.e uptill BW.

There are 3 tables.

1st table :B:AN
2nd Table :AT:AY
3rd Table:AZ to BW ........................................this table is needed to be build i.e to get data automatically which is simply the breakdown of AM Column data (I have shown differece zero in AO column as well for better understanding)