Extract Matches and Differences

In this tutorial, we’ll show you how to extract matches and differences using lists.

The article is a part of our in-depth tutorial about how to compare two columns in Excel series.

In most cases, we are using various conditional formatting tricks or custom formulas. Furthermore, you can choose time-saving solutions.

Our free excel add-in, DataXL, will help you to save your time. Furthermore, if you are in data cleansing, we recommend using automated solutions. Coding is a basic life skill.

Okay, here we go! The installation process can take only a few seconds. As first, it’s necessary to show the Developer tab on the ribbon.

If you need further information about the steps, please check this video.

After the successful installation, you’ll see the DataXL tab on the ribbon.

How to get matches and differences?

Our goal is to make advanced methods simpler and more transparent. In some cases, you can use Excel automation instead of boring formulas and functions.

Extract the records from Range1 that coincide with Range 2

The first example is simple. Just create two lists using random data.

Steps to extract matches and differences from two lists:

1. Go to the ribbon, locate the DataXL tab and select the Ranges icon.
2. From the drop-down list choose the Compare Ranges function

3. After clicking the icon a new userform will appear.

4. Select the data sources

5. Under the Comparison Methods tab choose Option 1

6. Select the output cell

7. Finally, Click OK

Extract the differences from Range1 which are not in Range 2

The difference between Example1 and Example 2 is small.

In this case, we have to choose ‘find the rows of R1 which are not in R2’ under the Comparison methods tab.

After clicking OK, you’ll get the result below:

Extract the differences from List2 which are not in List1

Last but not least, we’ll examine the third option.

In this example, select the ‘find the rows of R2 which are not in R1’ option under the Comparison methods tab.

The output is cell L4, let us see the result:

Final thoughts – Feel the Power of VBA

Believe it or not, VBA is a powerful programming language. Not just for Excel. It is worth to use add-ins, macros, or custom functions (user-defined functions). All you need is logical and critical thinking to automate your tasks. Your daily work will be easier! Guaranteed.