• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macros to calculate month on month change in mix design in FG code

R

Ruchit Jain

New Member
I have a data in excel for two months (current and previous)(October & November)
I want to compare if there is any change in the mix design in the current month as compared to the previous month.
The FG code is in column H
So in the previous month production has happened for certain FG codes then I want to know for the same FG codes what were the changes in the mix design in the current month.
From Column K to Column DH, we have consumption data under the Actual tab for the load quantity in column J for a particular FG code in column G

Please refer below screen shot
Please help me in writing macro so that it quickly calculates the difference in the mix design in a given FG code of the previous month.
82040
 

Attachments

Ruchit Jain
Could You verify next Your writings?
# I have a data in excel for two months (current and previous)(October & November)
How do You set those months? ... eg if You open Your file 14th of Dec, then current month is ... December.
# if there is any change in the mix design ... what/where is the mix design?
# The FG code is in column H but Your sheet has Grade in that column ...
# So in the previous month production has happened for certain FG codes then I want to know for the same FG codes what were the changes in the mix design in the current month. ... Could You give few clear samples - what?
# under the Actual tab ... Your file has one tab, which have different name ... hmm?
# Please refer below screen shot ... as written above
# Please help me in writing macro so that it quickly calculates the difference in the mix design in a given FG code of the previous month.
Which is more important - speed or correct results?
# please support in developing macro for this report. ... Do You have a sample of expected report somewhere?
 
Could You verify next Your writings?
# I have a data in excel for two months (current and previous)(October & November)
How do You set those months? ... eg if You open Your file 14th of Dec, then current month is ... December.
*This data is extracted from ERP by giving date range as the input. Since we want month on month change then we give the input as start date-01-Oct-2022 End date- 30-Nov-2022. The output is in excel format which I shared earlier as an attachment.
# if there is any change in the mix design ... what/where is the mix design?
*The mix design starts from column K to Column DH. The material codes starts from K9 to DG9.
So mix design has material codes and their quantity. So if in a particular FG code few material codes are not there then the quantity will be blank under that material code.
# The FG code is in column H but Your sheet has Grade in that column ...
*sorry for incorrect information. The FG code is in column G.

# So in the previous month production has happened for certain FG codes then I want to know for the same FG codes what were the changes in the mix design in the current month. ... Could You give few clear samples - what?
82046
*Please refer above screen shot. For a given FG code we have Load Quantity(column J) in cubic meters and mix design starts from column K. Several columns are hidden between column K to column CX, I have intentionally hidden it to show you the mix design as the hidden columns have blank quantity.
# under the Actual tab ... Your file has one tab, which have different name ... hmm?
*Please refer above screen shot. In cell K10 there is "Actual" written and in L10 there is "Target" written so the difference between Actual Quantity and Target Quantity is the production variation. In my report I am just concerned with the "Actual" Quantity and it's difference month on month.
All the quantities are in kilograms for the complete volume (in cubic meters) mentioned in column J
For example,
The quantity for CA10MM is 3703 kilograms(cell K11). This quantity is for volume 6.5 cubic meters (cell J11)
Now a particular FG code say FGDBU003 (cell G11) has volume of 69.5 cubic meters in October month. So we want to find average quantity of all raw material( CA10MM, CA20MM, FARSAND etc..) per cubic meter and find the difference of these quantities in the November month for the same FG code. This I have to do for all the FG codes. It may happen that in November month that particular FG code has not been produced, so in that case it will give error.


# Please refer below screen shot ... as written above
# Please help me in writing macro so that it quickly calculates the difference in the mix design in a given FG code of the previous month.
Which is more important - speed or correct results?
* Correct results are priority but speed is also essential as the file attached is of one location. There are 80 such locations in our organization. So if it is slow then we will not get the decision making data on time.
# please support in developing macro for this report. ... Do You have a sample of expected report somewhere?

I don't have the sample of expected report. It will be a first of a kind after it is developed.
 

Attachments

  • 1671011791759.png
    1671011791759.png
    10.6 KB · Views: 0
Ruchit Jain
# Why do You give misinformation like (current and previous)... or ERP?
... do those matter for any calculation?
# Could You give few clear samples - what?
... how to calculates something based Your one line ... sample?
# under the Actual tab ... Your file has one tab, which have different name ... hmm?
... You wrote something ... which maybe You could figure?
# You wrote please support in developing macro for this report.
... and You have not any idea ... what kind of report would You get?
 
# Why do You give misinformation like (current and previous)... or ERP?
... do those matter for any calculation?
*Because you only ask for misinformation. Why did you ask for how to set those months?
That is the reason I have to tell you about ERP software.
# Could You give few clear samples - what?
... how to calculates something based Your one line ... sample?
*It is not one line sample. I have shared the whole file just open it.
# under the Actual tab ... Your file has one tab, which have different name ... hmm?
... You wrote something ... which maybe You could figure?
*Okay it has only one tab. In that tab, there is one header written as "Actual" in cell K10
# You wrote please support in developing macro for this report.
... and You have not any idea ... what kind of report would You get?
* How would I have a sample report? I would have not posted my question here if I had that sample report.
 
Ruchit Jain
Okay ... I see.
You've written something.
I've not found - how You would calculate month on month change?
Finally, You expect that someone else would offer (Your called) this report without having Your idea - what it could be?
It's a challenge.
 
You must log in or register to reply here.
Back
Top