Comparing Two Lists or Datasets in Microsoft Excel

Originally published in The Cooperative Accountant, Fall 2021 Issue

Many tasks that accountants and auditors perform require comparing two lists, or datasets, to find matching, non-matching, or missing values.  Some of these tasks include bank statement reconciliations, general ledger, and account reconciliations.  Auditors also compare lists or datasets for analysis to investigate anomalies or identify trends in data, as well as to provide audit evidence.  The ability to analyze entire datasets instead of just samples can improve the quality of the audit.  For example, it can aid in duplicate detection of transactional data, identify outliers or anomalies, or detect missing information.

In Excel, like many things, there are multiple ways to accomplish that task.  Some of the methods include the use of conditional formatting, creating formulas, and the use of add-in’s such as Power Query.  This article will examine some of these various methods and offer guidance on how to select the method that best fits the requirement at hand.  It will not address all available methods.  The article is also not meant to provide tutorials on these methods.  Tutorials can be found on the internet and in various Excel books and publications.  This article will describe the methods so that you know what functions are available, and then what to search for when looking for tutorials. 

The techniques discussed in this article include:

1. Quick conditional formatting to compare two columns of data
2. Match Data using Row Difference Technique
3. Row Difference using IF Condition
4. Matching data using the MATCH function
5. Range sizes in data set fluctuate – use Tables
6. VLOOKUP and XLOOKUP formulas
7. Creating a composite column
8. Using Excel Power Query

1. Quick conditional formatting to compare two columns of data

This method might be the quickest and most simple method.  It will allow you to highlight a cell or range of cells based upon defined criteria.  A Duplicate Values setting box is available in the Conditional Formatting drop down list, where you can define the formatting and selection of Duplicate or Unique values.  Formatting of values identified can then be defined for both Duplicate or Unique values for datasets in both lists.

2. Match Data using Row Difference Technique

When comparing two lists of data, select both columns of data, press F5 key on the keyboard, select the “Go to special” dialog box.  Then select “Row difference” from the options.  Matching cells of data across the rows in the columns are in white color and unmatched cells appear in grey color.

3. Row Difference using IF Condition

The IF Condition formula states if there is a match in the row when comparing two lists of data.  If there is a match, the result of the formula will be “Matching” and if not then “Not Matching”.  The formula would look something like this:  =IF(A2=B2, “Matching”, “Not Matching”).  The formula would need to be copied down the row of cells. 

To read the full article, visit https://nsacoop.org/publications/tca or NSAC Connect