How to use Excel formulas to compare multi-column lists – TechRepublic

How to

Register for your free TechRepublic membership or if you are already a member, sign in using your preferred method below.
We recently updated our Terms and Conditions for TechRepublic Premium. By clicking continue, you agree to these updated terms.
Invalid email/username and password combination supplied.
An email has been sent to you with instructions on how to reset your password.
By registering, you agree to the Terms of Use and acknowledge the data practices outlined in the Privacy Policy.
You will also receive a complimentary subscription to TechRepublic’s News and Special Offers newsletter and the Top Story of the Day newsletter. You may unsubscribe from these newsletters at any time.
All fields are required. Username must be unique. Password must be a minimum of 6 characters and have any 3 of the 4 items: a number (0 through 9), a special character (such as !, $, #, %), an uppercase character (A through Z) or a lowercase (a through z) character (no spaces).
How to use Excel formulas to compare multi-column lists
Your email has been sent
Duplicates in the same column are easy to find by sorting, filtering, and using conditional formatting. When none of those are an option, try this simple formula solution.

Finding duplicate values in the same column is easy; you can sort or apply a filter depending on the circumstances. Finding duplicates that span multiple columns is a tad more difficult. A sort can work, but then you must spot the duplicates. So, while it’s better than no solution at all, it’s not a great solution. Conditional formatting might be an option, but if you already have several formats, it too might not be the best solution. If you want a solution that screams out, “Here I am! I’m a duplicate!” why not do just that? In this article, I’ll show you a simple expression solution that might be easier to implement and more effective than conditional formatting.
I’m using Excel 2016 (desktop) on a Windows 10 system, but this solution will work in older versions. You can work with your own data or download the demonstration .xls and .xlsx files.
Note: this is the second of three articles in a series. For the previous articles, see How to use Excel’s conditional formatting to compare lists.
Let’s take a quick look at a simple example. The sheet shown in Figure A contains a column of dates and a column of initials. A few dates occur more than once, and a few initial sets also repeat; those represent duplicates within a single column. We’re interested in rows that repeat the same date and the same initial set. That’s what I mean by a multi-column duplicate. And, we’re assuming you don’t want to use a custom conditional rule.

It’s easy to spot the duplicates–rows 4 and 8 and rows 5 and 9–in such a simple sheet, but what if you had hundreds or thousands of rows to check? A filter works, but it’s a vulnerable solution. In this case, there are three distinct dates. That means a user must review at least three sets of records to find duplicates. Even then, you must trust your user to spot them! The built-in conditional formatting rule would highlight everything because all the values are duplicates! You might try an advanced filter or even a custom conditional format rule, but both require some hoop-jumping.
SEE: Microsoft SharePoint: A guide for business professionals (Tech Pro Research)
Let’s take a different approach from sort, filtering, and custom formatting. Instead, we’ll use COUNTIF() to count the number of combined values and an IF() function to return an appropriate message. The first step is to concatenate all the columns, so you’re not really comparing; you’re counting.
To concatenate the values, enter the following formula in D3 and copy it to the D4:D10:
This technique is flexible, and you can concatenate several columns by combining them with the & character. Excel returns the serial values (Figure B) but that won’t interfere with the technique. (Time values might be problematic, depending on how they’re entered.)

Now we’re ready to count the concatenated results in column D, so enter the formula
=IF(COUNTIF($D$3:D3,D3)>1, "Here I am! I'm a duplicate!", "Original")
in E3 and copy to the remaining cells. As you can see in Figure C, finding multi-column duplicates is as easy as sorting by column E (although this simple example doesn’t require that extra step). You could find duplicate records or subsets of records. You determine the outcome by deciding which columns to concatenate.

The COUNTIF() function counts the number of times the concatenated result occurs within the extended range. If the count is greater than 1, the formula returns the string “Here I am! I’m a duplicate!”; otherwise, the formula returns the string “Original.” It’s important to note that the first occurrence of a duplicate evaluates as original; only duplicates following the original return the duplicate string. You could reduce the visual noise a bit by returning an empty string instead of the string, “Original” when there’s no duplicate.
This technique easily adapts to additional columns. Simply add each column to the concatenating formula. Of course, there are other ways to identify multi-column duplicates in Excel, but this one requires no specialized knowledge and is incredibly easy to implement.
SEE: Tap into the power of data validation in Excel (free PDF) (TechRepublic)
In a subsequent article, we’ll continue this study of finding duplicates with a more complex example set–comparing multi-column lists in different data sets.
I answer readers’ questions when I can, but there’s no guarantee. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at
Also see:
How to use Excel formulas to compare multi-column lists
Your email has been sent
Your message has been sent
TechRepublic Premium content helps you solve your toughest IT issues and jump-start your career or next project.
Windows 11 gets an annual update on September 20 plus monthly extra features. In enterprises, IT can choose when to roll those out.
Edge AI offers opportunities for multiple applications. See what organizations are doing to incorporate it today and going forward.
This is a complete guide for Apple’s iPadOS. Find out more about iPadOS 16, supported devices, release dates and key features with our cheat sheet.
Discover data intelligence solutions for big data processing and automation. Read more to explore your options.
Whether you are a Microsoft Excel beginner or an advanced user, you’ll benefit from these step-by-step tutorials.
Recruiting a Scrum Master with the right combination of technical expertise and experience will require a comprehensive screening process. This hiring kit provides a customizable framework your business can use to find, recruit and ultimately hire the right person for the job. This hiring kit from TechRepublic Premium includes a job description, sample interview questions …
Knowing the terminology associated with Web 3.0 is going to be vital to every IT administrator, developer, network engineer, manager and decision maker in business. This quick glossary will introduce and explain concepts and terms vital to understanding Web 3.0 and the technology that drives and supports it.
While the perfect color palette or the most sublime button shading or myriad of other design features play an important role in any product’s success, user interface design is not enough. Customer engagement and retention requires a strategic plan that attempts to measure, quantify and ultimately create a complete satisfying user experience on both an …
IIoT software assists manufacturers and other industrial operations with configuring, managing and monitoring connected devices. A good IoT solution requires capabilities ranging from designing and delivering connected products to collecting and analyzing system data once in the field. Each IIoT use case has its own diverse set of requirements, but there are key capabilities and …


Leave a Reply

Your email address will not be published.