Find Duplicates Between Two Columns
We begin with two columns of product codes, and we need to determine if any of the codes in “Segment 1” are also contained in the list of codes of “Segment 2”.
The first step is to select the data from each list. To select multiple, non-contiguous ranges, hold down the CTRL key as you select each subsequent list.
Next, from the Ribbon, select Home (tab) – Styles (group) – Conditional Formatting – Highlight Cells Rules – Duplicate Values.
From the Duplicate Values dialog box, select whether you wish to locate the duplicate values or unique values, as well as the color behaviors you wish to exhibit when a duplicate (or unique) value is encountered.
Now we see the values that occur across these two lists.
NOTE: This technique does not compare duplicates that are exclusively between the two lists. If a duplicate entry exists in only one of the two lists, the entries will be flagged. Think of it as if the two lists are being seen as a single list where all duplicates are being discovered.
Locate Duplicate Rows in a Table
A more complex example is when you want to compare an entire row’s content with another row’s content to determine if those rows match in their entirety.
We can’t use the previous method where we selected all cells because this would examine each cell separately. We want to examine the entire row as a single value.
To do this, we will create a “helper column” that concatenates the entire row’s data into a single value. We can then examine these results using the previous method to identify the duplicates.
To concatenate each’s rows data into a single value, we’ll utilize Excel’s CONCAT Function.
=CONCAT(A4:C4)
Fill the newly created CONCAT function down the length of the table.
We’ll apply Conditional Formatting’s “Duplicate Values” action on this list of concatenated data.
To make this more interesting, we’ll hide the concatenated data and use the cell highlight as a flagging mechanism to identify the duplicate rows.
To hide the cell’s contents (this is a neat trick for a variety of situations), select the cells and apply a custom number format (CTRL-1) of three semi-colons.
This is Excel’s secret code for making cell content invisible.
Next, make the column narrower to where the cells take on a more square-ish shape.
Featured Course
Black Belt Excel Package
Using Excel Formulas to Locate Duplicate Values
Instead of coloring the cells that contain duplicate values, suppose you want to show something like “True” or “False”, or return a count of the number of times an entry appears in a list.
We can use Excel functions to create more sophisticated formula-based solutions.
Using an Excel Formula to Locate Duplicate Values in a Column
Let’s start with using a formula to identify duplicates within a single column.
To determine the number of times an entry occurs in a list, use Excel’s COUNTIF Function.
The COUNTIF Function has two arguments, the range of cells to be examined for duplicates, and the item being examined.
=COUNTIF($B$4:$B$18, B4)
If you’re not interested in the number of times a value occurs in the list, rather than whether it is a duplicate or not, we can turn the numeric responses into “True/False” responses by checking if the result is greater than 1.
=COUNTIF($B$4:$B$18, B4) > 1
Using an Excel Formula to Locate Duplicate Rows in a Table
To use a formula to locate duplicate rows of data in a table, combine the previous examples of using a “helper column”, the CONCAT Function, and the COUNTIF Functions.
Begin by creating a “helper column” that concatenates all values from a single row.
=CONCAT(B4:D4)
Next, use the COUNTIF Function to determine if each value exists more than one time in the list of helper results.
=COUNTIF($E$4:$E$18, E4) > 1
NOTE: These two steps could be combined into a single formula, but it’s often easier to work each step as separate formulas to keep track of the logic more easily.
Using Symbols to Represent True and False in Excel
Let’s make this interesting by displaying emojis (i.e., symbols) in place of the “true” and “false” results.
Wrap the COUNTIF Function inside an IF Function like so…
=IF(COUNTIF($E$4:$E$18) > 1,Now, for the [True] argument, we’ll press Win-period (Win-.) to bring up the Windows Emoji Library and select a symbol we wish to reflect a “hit” when we have a duplicate entry.
We’ll use a symbol for “true” and display nothing for “false”. Make sure you place the Emoji in a set of quotation marks.
To put a bit of polish on this, we’ll hide the “helper column” (Column E) and color the symbols red using a red font color for the cells in Column F.
Highlight Entire Row Based on a Condition
Suppose you wish to highlight the entire row of duplicate information based on the results of the previously created IF – COUNTIF formula.
Since we have already identified the duplicate rows using the formulas in Column F, we’ll check to see if a result in Column F is an Emoji symbol and if it is, color the entire row of the table in column B thru D.
- Select the table of original values (B4 thru D18).
- Select Home (tab) – Styles (group) – Conditional Formatting – New Rule.
- For the Rule Type, select “Use a formula to determine which cells to format”, and enter the following formula (making sure to lock the column reference but not the row reference):
- Set the formatting to apply a light red cell fill.
=$F4= "⛔"
The results are as follows:
Featured Course
Master NEW Excel Functions in Office 365 & Office 2021
Generate a List of Duplicates from a Table
Instead of flagging or highlighting duplicates in a table, suppose you need to generate a separate list of values that are duplicates.
Using the “helper column” of concatenated data, we can use the FILTER Function to reduce the list of source data using the COUNTIF Function as the filter criteria, only keeping entries that occur more than 1 time.
=FILTER(B4:D18, COUNTIF(E4:E18,E4:E18) > 1, "")
Although this DID return a list of the duplicate entries, it would be nice to see them sorted in a way where each duplicate is next to its corresponding duplicate entry. This can be done by wrapping the entire formula inside a SORT Function.
=SORT(FILTER(B4:D18, COUNTIF(E4:E18,E4:E18) > 1, "") )
And, if you don’t need to see the duplicates represented for each instance, you can reduce the list by incorporating a UNIQUE Function.
=SORT(UNIQUE(FILTER(B4:D18, COUNTIF(E4:E18,E4:E18) > 1, "") ) )
If you just want to remove duplicates from your data, check out the 3 Methods you can go about it.
Practice Workbook
Feel free to Download the Workbook HERE.
Featured Course
Power Excel Bundle
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
