Compare 2 sets of data by letter or word & highlight mismatches [vba]

We analysts like to compare. If you ever want to keep an analyst busy, just give her 2-3 options. She won’t return to your desk until the cows come home. My wife uses this trick all the time. Picture this:

[In late 2013]
Me: I want to buy a new phone
She: Do you want Nexus 5 or Galaxy S5 or iPhone 5s?

Its late 2014 and I am not done comparing.

So today, let’s talk about an interesting comparison scenario.

Comparing by letter or word

Imagine you are looking at 2 lists like this and you want to know where items differ. Not which items, but where.

That means, you want to know which letters or words in each line are different.

VBA to the rescue

Unfortunately, none of the standard features of Excel (formulas, conditional formatting, pivot tables etc.) can help us with this situation. But we don’t have to give up. We can use a simple VBA macro to instantly compare 2 lists and highlight mismatched letters or words.

[Related: How to compare 2 lists in Excel, a quick round up of techniques]

A quick demo of our comparison macro:

How does this macro work?

When you set out to create macros like this, the first step is to define basic algorithm (logical steps in plain English). To compare 2 sets of data, we need to do below:

1. For each item in list 1
2. Get corresponding item in list 2
3. If they don’t match
4. For word match
   1. For each word in first text
   2. Get corresponding word in second text
   3. Compare
   4. If not matched, highlight in red color
   5. Repeat for other words
5. For letter match
   1. Find the first mismatched letter
   2. Highlight all the letters from that point in second text
6. Repeat for next item in list 1
7. Done

Once you write down this logic, we simply go ahead and implement it in VBA code.

The exact workings of the macro are somewhat complex. So I made a video explaining how the code works & what it can do. Please watch it below.

Video explaining the comparison macro

[see this video on our YouTube Channel]

Download Example Workbook

Click here to download the comparison macro workbook. Examine the code to understand how it is constructed. Feel free to extend it to suit your work needs.

Do you compare lists like this?

Every now and then, I end up having a situation where I need to compare by letter or word. I find VBA macro based solution to be perfect for this.

What about you? Do you compare lists? Where do you struggle with such comparisons? How would you use this macro? Please share your thoughts & tips in comments.

Become incomparable, learn VBA

While VBA is pretty powerful & awesome, not many venture beyond the basic recorded macros. You can transform the work, career & skills by learning VBA. It is not at all difficult and anyone can learn it. Start with below links.

• Introduction to VBA & 5 part crash course
• What is a macro and how to get started with VBA
• 40+ Example VBA macros
• Course: Online VBA Classes from Chandoo