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

Share

Facebook
Twitter
LinkedIn

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.

Compare 2 lists of data and highlight mismatched letters or words in Excel - How to?

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:

Compare two texts in Excel and highlight unmatched letters or words using VBA Macros - Demo

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.

 

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

2 Responses to “Weighted Sorting in Excel ”

  1. Oleg says:

    Just add a column calculating the "performance" or whatever is your criteria and sort by it? No?
    have no patience to waste 13min. Save your time too.

  2. Andrew says:

    Just thought I would mention, the "weird" custom sort behavior mentioned at 5:45 where "% return" doesn't appear to be sorting is because the "August Purchases" field has the sort preference and since these are such unique values, no additional sorting is possible on the "% return" field. If there were two entries that had the same "Customer Since" year AND the same "August Purchases" amount, THEN you would see a sorting of the "% return" on these two entries.

Leave a Reply