How to compare two pivot tables ?

[Kolovez]

Hi all,

I got 1 pivot table showing Sales in the top 10 selling stores (rows) and top 10 selling products (columns)
These numbers are based on 2017 numbers and i used the value filter to find make the table.

I would like to compare how the same top 10 stores and products from 2017 are performing in 2018, but when i make the pivot table for 2018 and select value filter "top 10", then the stores and products are necessarily the same as in 2017.

Is there a way to lock to the 2017 table rows and columns and then just update the values with new dates?

one last annoying thing.. the pivot table is created using external data from a cloud, which means i don't have access to the raw data.

best,
Jakob

 

[Kolovez]

I have an idea, but i still need help to execute it.

I have the static 2017 numbers with top 10, those are just copy pasted as values (not pivot).
Then i get the top 50 values from 2018 in a pivot table.

Then i make a new table that use the text "store" and "product name" from the 2017 table and search/match the same store and product name in the top 50 list for 2018, and then shows me the new number.

So i guess all i need is a formula that can search for 2 specific conditions "store name" and "product name" then give me the value for that combination, if no value is to be found then the product is probably delisted and the error message could just be "value not found".

anyone know how to construct such a formula?

 

[Kolovez]

The problem has been solved now.

For those curious, here are the steps:

1. Make static 2017 table with top stores and products
2. Insert pivot with 2018 numbers (use top 50 instead of 10, to ensure the numbers are there)
3. make a table similar to the 2017 table with same columns and rows, then use the index and match formula.
4. Make new table that compare the static table 2017 with the table in step 3.

Done.

the index & match formula is highly recommended to know by the way, and its super simple once you get it.
Index shows the intersection of the column and row that you specify. To find the correct row and column you are looking for you use the match formula.
Here is a link to a great video explaining it: https://www.youtube.com/watch?v=kxeSS8n3WNI