Hi ALL,
I need some help to create a macro to compare quantities of 2 worksheets and then create a new worksheet showing quantity change. See attached sample excel file and work step when I done this exercise manually. The macro will help reduce time spent and possible error in manual manipulation.
I have only limited knowledge in creating VBA/macro. I appreciate if someone can help me on this Application.
dashboard.jpg
MY WORK FLOW BELOW:
Workbook names information:
Previous Design QTYs (worksheet name - Initial_MTO)
New Design QTYs (worksheet name 2nd_MTO), this will only contain values on column F(Current Design QTY)
Output Current QTYs (worksheet name Top-Up 1_MTO)
Columns I(Current Order) & K(Delta) have formula
Colum E(PartNo) is the Primary key
STEP 1:
Copy Initial_MTO worksheet and name it as Top-Up 1_MTO. Note all item number will stay the same and no sorting for traceability for each Top-Up stages.
Copy values on column I(Current Order) and paste value on column G(Previous Order)
Delete all values on column F(Current Design QTY)
Retain values on column H(Contingency) & column J(Surplus)
Leave the calculated values on column K(Delta)
STEP 2:
Copy all data from 2nd_MTO worksheet and append to the end entry of Top-Up 1_MTO worksheet
STEP 3:
Do advance combined row using column E(PartNo) as the PRIMARY KEY, columns F(Current Design QTY) & column G(Previous Order) as CALCULATE(SUM)
Highlight new items that are not included on worksheet Initial_MTO)
Adjust numbering on column A(Item No.) for new added items.
Current Order = Current Design QTY + Contingency Surplus (QTY)
Delta = Current Order Previous Order
Columns H(Contingency) & I(Surplus QTY) will be blank, input manually at a later stage.
Thank you in advance.
Bookmarks