Forum Discussion
Compare 2 sheets with pivot or not what dropped and what is added in the new month
Hi Team,
I am trying to compare two sheets (Feb & March) based on Column "T". The difference between February and March where I need to know how the data changed in March. I need to do this check every month so the next will be to compare March and April.
For Example I have "11C2222" (row 4) Column T in tab "Feb" but it dropped in tab "Mar".
I have also "11D4444" (row 4) Column T in tab Mar who was added in March.
Column T is the only column in both sheets where we have unique values so I think we need to use it to provide the differences between the 2 tabs (Feb & Mar).
Please find attached the file.
If pivot is not possible is there another way?
I tried with =MATCH row function but I have all data and I need to know only the differences between the 2 sheets.
Thank you in advance! I would really appreciate it!
Steiny88 such tasks are easiest achieved with Power Query, as demonstrated in the attached file. Would that work for you?
5 Replies
Steiny88 such tasks are easiest achieved with Power Query, as demonstrated in the attached file. Would that work for you?
Riny_van_Eekelen I think it's working fine, but can you please tell me how you did it so I can follow the same steps in my actual worksheet?
Thank you!
Steiny88 In short, these were the steps I followed:
1) Format both data ranges as tables
2) Connect to each of them with Power Query (PQ)
3) Merge (as new) the old table with the new one, Left Anti Join
4) Merge (as new) the new table with the old one, Left Anti Join
5) Load both queries back to Excel.
A "left anti join" will only keep rows that exist in the first table. i.e. the one at the top in the Merge screen. Thus, in PQ, the first table that sits at the top is called Left. And the last one at the bottom is called Right. Don't ask me why 🙂
Thus in step 3) you identify the ones that were removed and in step 4 the ones that were added.
Now, if you are totally unfamiliar with PQ, you will not understand much of this. Then you need to put some effort into learning the basics. A good place to start would be on the site in the link below.
https://exceloffthegrid.com/power-query-introduction/
