Changelog - to see what rows been added or removed between 2 sheets?

[sbi85]

Hey all,

This is somethings I've been trying to solve but never managed.

We have a report about (let's say) product usage that was pulled at Week 1 - stored in Worksheet1. Then we pull a report on this again a week later - stored in Worksheet2. There are new products entering the second week and there are ones that are not used anymore so they went away (plus there are some with no change).

To visualise it this way (it's better to view this in the attached example file though):

Worksheet1(Week1)

ID	Entity	Date refreshed
0064J00000D6HhL	A	05/05/2021
0064J00000Dkcbe	B	05/05/2021
0064J000007Ho9l	A	05/05/2021
0064J00000Dl58B	A	05/05/2021
0064J00000D6621	B	05/05/2021
0064J00000DlNOn	B	05/05/2021
0064J00000CaYCs	B	05/05/2021
0064J000007MQuN	A	05/05/2021
0064J00000A1seP	A	05/05/2021
0064J00000Dl84x	B	05/05/2021

Worksheet2(Week2)

ID Entity Date refreshed
0064J000007MQuN A 12/05/2021 => retained
0064J00000A1seP A 12/05/2021 => retained
0064J00000D6HhL A 12/05/2021 => retained
0064J000007Ho9l A 12/05/2021 => retained
0064J00000DlNOn B 12/05/2021 => retained
0064J00000Dl84x B 12/05/2021 => retained
0064J000009N5rN A 12/05/2021 => added
0064J00000CbEu1 A 12/05/2021 => added
0064J00000CGAHa B 12/05/2021 => added
0064J00000DJw9h B 12/05/2021 => added
0064J00000DlRNH A 12/05/2021 => added
0064J00000CFzcD B 12/05/2021 => added


Now what I'd like to have on Worksheet3 is the list of changes from Week1 to Week2. To list the rows that were either added or removed for Week 2.
So something like this:

ID	Entity	Date refreshed	Change
0064J000009N5rN	A	12/05/2021	Added
0064J00000CbEu1	A	12/05/2021	Added
0064J00000CGAHa	B	12/05/2021	Added
0064J00000DJw9h	B	12/05/2021	Added
0064J00000DlRNH	A	12/05/2021	Added
0064J00000CFzcD	B	12/05/2021	Added
0064J00000Dkcbe	B	05/05/2021	Removed
0064J00000Dl58B	A	05/05/2021	Removed
0064J00000D6621	B	05/05/2021	Removed
0064J00000CaYCs	B	05/05/2021	Removed

(Entity = think of it as companies)

How to track the changes from Worksheet1(Week1) to Worksheet2(Week2) as sort of a changelog in a third sheet?

Please let me know how you would solve this or any advice you can give!
Any help is greatly appreciated.

Balint

--------------
As for a bit more detailed context (perhaps it's helpful) the data in Worksheet2 would be put into Worksheet1 every week and the new week's data put into Worksheet2 to always compare the last 2 weeks of data.

And in case this is helpful for someone, (but I felt it's more difficult to understand what the data is about this way), the IDs are actually Salesforce Opportunities and I am trying to track the week-on-week changes of potential sales to see what potential deals are being added to the pipeline and what is getting removed from the pipeline. And I am pulling reports into excel on a weekly basis to analyse the current status of pipeline.

[alansidman]

Are you still running MAC version of Excel? Power Query is an option if you are on a Windows Version. You can run an Anti Join. This is not an option in MAC versions however.

[sbi85]

No, I do not thankfully
I'll look into Anti Join. I'm a pretty basic user and only used Power Query once.

[alansidman]

Please update your version of Excel in your profile. This will make a difference in the solution provided.

[sbi85]

Done that. Thank you!

[alansidman]

Attached is your solution in Power Query. Multiple queries and joins.

[sbi85]

This is brilliant man. So brilliant that even I was able to reproduce it with my meager skillset.
Much appreciated.

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.