Editor & Guest Writer Peggy Maranan, Ph.D. DEMCO Director, Finance 16262 Wax Road Greenwell Springs, LA 70739 Phone 225.262.3026 Cell: 239.887.0131 peggym@DEMCO.ORG
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 4
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 Fall 2021 | The Cooperative Accountant
