This tutorial will show you how to find duplicate values in two given columns in Excel. Or How to compare two columns and find duplicate entries in Microsoft Excel worksheet. And you will learn two methods to compare columns so that the duplicate values can be highlighted or listed in a range of Cells.
Assuming that you need to compare two columns (Column A and Column B) to get the duplicate values in your worksheet, you can find duplicate values in two columns with Excel Formula, or Excel VBA Macro code.
Table of Contents
Method 1: Find duplicate values in two columns with Excel Formula
To compare two given columns and find duplicate values in Excel, you can use a combination of the IF function, the ISERROR function, and the MATCH function to create an Excel formula. So you can use the following formula:
Now you need to type this formula in Cell C1, press Enter key, drag AutoFill Handle down to Cell C2:C4, you will see all of the duplicated values are displayed in Column C.
Method 2: Find duplicate values in two columns with VBA Macro code
If you are familiar with the programming language and you can use a Visual Basic Macro to compare the value in two columns and then find duplicate values, just refer to the following steps:
1# click on “Visual Basic” command under DEVELOPER Tab.
2# then the “Visual Basic Editor” window will appear.
3# click “Insert” ->”Module” to create a new module
4# paste the below VBA code into the code window. Then clicking “Save” button.
Sub FindUplicatesinTwoColumns() Set Range1 = Application.Selection Set Range1 = Application.InputBox("Select the first range in one column:", "FindUplicatesinTwoColumns", Range1.Address, Type:=8) Set Range2 = Application.InputBox("Select the second range in another column:", "FindUplicatesinTwoColumns", Type:=8) For Each R1 In Range1 xValue = R1.Value For Each R2 In Range2 If xValue = R2.Value Then If R3 Is Nothing Then Set R3 = R1 Else Set R3 = Application.Union(R3, R1) End If End If Next Next R3.Interior.ColorIndex=3 End Sub
5# back to the current worksheet, then run the above excel macro.
6# Select the first range A1:A4 in Column A, click OK button, then select the second range B1:B4 in Column B, click OK button.
7# let’s see the result:
Method 3: Find duplicate values in two columns with Conditional Formatting feature
You can use conditional formatting with on a formula based on the COUNTIF function and the AND function to find the duplicate values in two specified columns and then highlighted them. Just do it following:
1# Select the entire Column A via click on the Column Header and then the column A will be highlighted
2# on the HOME tab, click the Conditional Formatting command under Styles group. Then select New Rules… from the drop-down menu list.
3# select Use a formula to determine which cells to format as Rule Type in the New Formatting Rule window
4# Type the following formula in the Format values where this formula is true: box
5# click the Format… button, then the Format Cells window will appear.
6# in the “Format Cells” window, switch to the Fill tab, choose the background color, and then click OK button.
7#you will be back to the New Formatting Rule windows and you can check a preview of the formatting you have selected. Then click OK button.
Then the conditional formatting rule will be applied to all values in two columns and highlighted the duplicate values.
- Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
- Excel ISERROR function
The Excel ISERROR function used to check for any error type that excel generates and it returns TRUE for any error type, and the ISERR function also can be checked for error values except #N/A error, it returns TRUE while the error is #N/A. The syntax of the ISERROR function is as below:= ISERROR (value)….
- Excel MATCH function
The Excel MATCH function search a value in an array and returns the position of that item.The syntax of the MATCH function is as below:= MATCH (lookup_value, lookup_array, [match_type])….
- Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
- Excel AND function
The Excel AND function returns TRUE if all of arguments are TRUE, and it returns FALSE if any of arguments are FALSE.The syntax of the AND function is as below:= AND (condition1,[condition2],…)…
- Highlight Rows
You will learn that how to change the color of the entire rows if the value of cells in a specified column meets your conditions, such as, if the value of cells is equal to or greater than a certain number or text values, then excel should be highlight entire rows or change a row color as you need.…
- Find Duplicate Rows
If you want to check the entire row that duplicated or not, if True, then returns “duplicates” value, otherwise, returns “no duplicates”. You can create a formula based on the IF function and the SUMPRODUCT function..…
- Highlight Duplicate Rows
this post will talk that how to highlight entire rows that are duplicates in excel 2016, 2013 or lower version. Or how to change the background color of duplicate rows..…
- Highlight duplicate values
this post will teach you how to highlight duplicate values in the range of cells in excel. Normally, you may be need to identify duplicate values with a range of cells in Excel. And there is one of the fasted way that is using conditional formatting feature in Microsoft Excel……