Results 1 to 4 of 4

Thread: How to compare two worksheets in a dynamic way

  1. #1
    Neophyte Derubeis's Avatar
    Join Date
    Apr 2019
    Posts
    2
    Articles
    0
    Excel Version
    2019

    How to compare two worksheets in a dynamic way



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

    I would like to
    Compare two worksheets( Giacenzetoday and Giacenzetoday2- see attached files)
    to find

    1. How many product are in the worksheet Giacenzetoday2 and not in Giacenzetoday (new products)
    2. How many product are in both of them Giacenzetoday2 and in Giacenzetoday (products in stock)
    3. How many product are not in both of them Giacenzetoday2 and in Giacenzetoday (products processed)

    I think I understand, with POWER QUERY, how to calculate point 1, 2 and 3

    My first big problem now is to make this comparison dynamic (Giacenzetoday3 with Giacenzetoday2 – Giacenzetoday4 with Giacenzetoday3 an so on… ) with a lot of comparisons between two worksheets (day by day or moment to moment…because the flux of product flow is continuos )
    And mostly
    Add up dynamically the list of product

    • New (all the new from the first worksheet to the last worksheet)
    • In stock (all the product yet in stock from the first worksheet to the last worksheet)
    • Processed (all the product processed from the first worksheet to the last worksheet)



    Thank you all
    Attached Files Attached Files

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    159
    Articles
    0
    Excel Version
    2019
    If you are familiar with PQ, then you may run Query Merges. Instead of running the Inner Joins, run the Anti joins to show what is missing from each of them. In the case of your example, you will need to run two of these. Look at my attachment to see this.
    Attached Files Attached Files

  3. #3
    Neophyte Derubeis's Avatar
    Join Date
    Apr 2019
    Posts
    2
    Articles
    0
    Excel Version
    2019
    Quote Originally Posted by alansidman View Post
    If you are familiar with PQ, then you may run Query Merges. Instead of running the Inner Joins, run the Anti joins to show what is missing from each of them. In the case of your example, you will need to run two of these. Look at my attachment to see this.
    Hi Alansidman
    I'm very newbie to PQ/PP and I don't understand your excel file "book8".
    It's composed of three queries and three worksheets with data.
    1)In Sheet4 we have the original data of "giacenzetoday"
    2) In Sheet3 we have the new items from the comparison of "giacenzetoday" and "giacenzetoday2"
    3) In sheet2 we have the processed items from the comparison of "giacenzetoday" and "giacenzetoday2"
    But Where are the "yet stocked" items from the comparison of "giacenzetoday" and "giacenzetoday2"
    And how to make dynamics this kind of analysis when a third survey will come?
    Sorry for my neophytes questions and thank you for help me


    Stefano

  4. #4
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    159
    Articles
    0
    Excel Version
    2019
    Did you look at the queries themselves to see how the tables were created? I am not sure I understand your question about "yet stocked" items.

    In each case, I brought in the tables from the original files.

    Suggest you get a hold of a book on Power Query to help better understand what you can do.

Posting Permissions

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