Results 1 to 3 of 3

Thread: Compare 2 worksheets and create new Updated worksheet showing quantity change

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Neophyte rxk's Avatar
    Join Date
    Dec 2018
    Posts
    2
    Articles
    0
    Excel Version
    2016

    Compare 2 worksheets and create new Updated worksheet showing quantity change

    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.

    Click image for larger version. 

Name:	dashboard.jpg 
Views:	16 
Size:	52.5 KB 
ID:	10574


    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.

    I hope my manual manipulation work step is clear. Thank you in advance.
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •