How to compare two worksheets in a dynamic way

Derubeis

New member
Joined
Apr 29, 2019
Messages
3
Reaction score
0
Points
0
Excel Version(s)
2019
[FONT=&quot]I would like to[/FONT]
[FONT=&quot]Compare two worksheets( Giacenzetoday and Giacenzetoday2- see attached files)[/FONT]
[FONT=&quot]to find[/FONT]

  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)
[FONT=&quot]I think I understand, with POWER QUERY, how to calculate point 1, 2 and 3[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]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 )[/FONT]
[FONT=&quot]And mostly[/FONT]
[FONT=&quot]Add up dynamically the list of product[/FONT]

  • 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)
[FONT=&quot]
[/FONT]

[FONT=&quot]Thank you all[/FONT]
 

Attachments

  • Giacenzetoday.xlsx
    14.1 KB · Views: 10
  • Giacenzetoday2.xlsx
    14 KB · Views: 18

alansidman

Member
Joined
Oct 1, 2018
Messages
362
Reaction score
1
Points
18
Location
Steamboat Springs
Excel Version(s)
O365
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.
 

Attachments

  • Book8.xlsx
    25.2 KB · Views: 13

Derubeis

New member
Joined
Apr 29, 2019
Messages
3
Reaction score
0
Points
0
Excel Version(s)
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.

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
 

alansidman

Member
Joined
Oct 1, 2018
Messages
362
Reaction score
1
Points
18
Location
Steamboat Springs
Excel Version(s)
O365
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.
 

Derubeis

New member
Joined
Apr 29, 2019
Messages
3
Reaction score
0
Points
0
Excel Version(s)
2019
Searching the solution...

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.


I looked for one solution but something is wrong ... do you help me?
 

Attachments

  • test1.xlsm
    76.6 KB · Views: 5
Top