Results 1 to 3 of 3

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

  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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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:	15 
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

  2. #2
    Conjurer Ed Kelly's Avatar
    Join Date
    Jul 2016
    Posts
    182
    Articles
    0
    Excel Version
    2016
    RXK,

    Here you go did it with M and pivot tables. (Power Query is a lot less temperamental than VBA). Included video so you can replicate. Note I would suggest pulling from a folder rather than a file, easier to swap in and out files. And of course this is totally refreshable (build it once and refresh new data into it). Note its raw MVP, you can tart it up cosmetically if you choose to use it.

    Knock 'em dead!

    https://www.youtube.com/watch?v=ElgnHuFFNWw
    Attached Files Attached Files

  3. #3
    Neophyte rxk's Avatar
    Join Date
    Dec 2018
    Posts
    2
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by Ed Kelly View Post
    RXK,

    Here you go did it with M and pivot tables. (Power Query is a lot less temperamental than VBA). Included video so you can replicate. Note I would suggest pulling from a folder rather than a file, easier to swap in and out files. And of course this is totally refreshable (build it once and refresh new data into it). Note its raw MVP, you can tart it up cosmetically if you choose to use it.

    Knock 'em dead!

    https://www.youtube.com/watch?v=ElgnHuFFNWw
    Thanks ED.
    I haven't used Power Query & Pivot Table much.

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
  •