PDA

View Full Version : How to compare two worksheets in a dynamic way

Derubeis
2019-04-29, 07:02 PM
I would like to
Compare two worksheets( Giacenzetoday and Giacenzetoday2- see attached files)
to find

How many product are in the worksheet Giacenzetoday2 and not in Giacenzetoday (new products)
How many product are in both of them Giacenzetoday2 and in Giacenzetoday (products in stock)
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

alansidman
2019-04-29, 08:31 PM
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.

Derubeis
2019-04-29, 10:26 PM
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
2019-04-29, 11:00 PM
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
2019-08-28, 06:59 PM
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?