• 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.

Compare 2 worksheets and create new Updated worksheet showing quantity changes

rxk

New Member
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. If possible the VBA will have the option to select the worksheets to be compared/consolidated and worksheet name of the output data.

I have only limited knowledge in creating VBA/macro. I appreciate if someone can help me on this Application.

MY WORK FLOW BELOW:

Workbook names and 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.
 

Attachments

Top