Excel VBA: Comparing Before & After Data [Dynamic Code - Will work on any dataset]

Excel VBA Reporting Project: Compare two versions of the same dataset and identify what's changed, what's new and what's deleted. With a small tweak to the code, it can be used to compare any dataset.

Skill Level: Intermediate - Advanced

Real life example dealing with Item Master Data. But, is applicable to any data set such as Supplier Master, Customer Master, Price Data, Inventory SOH etc.
Useful code for running audit checks and building data governance models.

We will look at different techniques to compare data starting with long-form way of using Nested For Loops. Then, we will perform the same task using Range.Find(). Later, we will replace Find with dictionaries, which is my preferred way.

Lastly, we will modify the code to make it work on any dataset. All you'll need to do is specify which column to lookup against.

Code can be found at my blog skillsandautomation.com:

Course file is uploaded to Github:

0:00 Intro
0:34 Premise
3:48 Assumptions
4:48 Video Layout
6:31 Section One: Nested For Loop
17:15 Section Two: Range.Find
23:40 Section Three: Dictionary
27:42 Section Four: Dynamic Code
31:43 Next Steps
Be the first to comment