Skip to main content

Compare two columns to find duplicates in Excel (Full guide)

Author: Xiaoyang Last Modified: 2023-11-10

Compare two columns to find duplicate values

To find duplicate values between two columns, there are several methods available depending on your requirements, such as highlighting duplicates for a visual overview or extracting them for in-depth analysis. In this section, we will introduce some quick tricks for accomplishing this task in Excel.

Highlight duplicates in two columns with Conditional Formatting

Highlighting duplicates across two columns in Excel is an efficient way to identify repeated data, particularly within large datasets where manual review is impractical. In this case, the Conditional Formatting is a useful feature to solve this task.

Step 1: Select the data in the column from which you want to highlight duplicates

In this example, I will select A2:A10, see screenshot:

Step 2: Apply the Conditional Formatting feature

  1. Click Home > Conditional Formatting > New rule, see screenshot:
  2. In the New Formatting Rule dialog box, please do the following operations:
    • 2.1 Select Use a formula to determine which cells to format from the Select a Rule Type list box;
    • 2.2 Type the following formula into the Format values where this formula is true text box;
      =COUNTIF($B$2:$B$10, A2)>0
    • Note: In the above formula, B2:B10 represents the data list you want to compare against, A2 is the first cell of the column where you want to highlight the duplicates from. This formula checks if the value in cell A2 is found anywhere in column B. Modify the cell references to suit your data.
    • 2.3 Then, click Format button.
  3. In the popped-out Format Cells dialog box, specify one color want to highlight the duplicate items. And click OK.
  4. When it returns the New Formatting Rule dialog box, click OK button.

Result:

Now, the values that are duplicates in both columns A and B are now highlighted in column A as following screenshot shown:

Tips:
  • The duplicate rule in Conditional Formatting is not case sensitive. So, Apple and apple would both be marked as duplicates.
  • If you want to highlight the duplicates from column B, you just need to select column B first, and then apply the following formula into Conditional Formatting:
    =COUNTIF($A$2:$A$10, B2)>0

Select and highlight duplicates in two columns with a powerful tool – Kutools

Sometimes, you may need to not only highlight but also select duplicates to copy and paste them into another location in your workbook. In such instances, Kutools for Excel’s Select Same & Different Cells feature is an ideal choice. It can identify the duplicate or unique values by highlighting and selecting the cells you need.

Note: If you want to use this Select Same & Different Cells feature, please download and install Kutools for Excel first.

Click Kutools > Select > Select Same & Different Cells, in the Select Same & Different Cells dialog box, please do the following operations:

  1. Select the source data and compared data in the Find values in and According to boxes separately;
  2. Select Each row under the Based on section;
  3. Choose Same values from the Find section;
  4. Specify a background color for highlighting the duplicate values under the Processing of results section;
  5. At last, click OK button.

Result:

Now, the values that are duplicates in both columns A and B are highlighted and selected in column A, ready for you to copy and paste into any desired cells. See screenshot:

Tips:
  • This feature supports case-sensitive comparison while ticking Case sensitive checkbox in the Select Same & Different Cells dialog;
  • If you want to select the duplicates from column B, you just need to swap the two selected columns in the Find values in and According to boxes of the Select Same & Different Cells dialog box;
  • To apply this feature, please download and install Kutools for Excel first.

Find and Extract duplicates in two columns with formula

To find and extract duplicates between two columns, you can use formula to pinpoint and pull out the duplicates.

Please copy and paste the following formula into a blank cell where you want to put the result, then drag the fill handle down the column to apply this formula to other cells.

=IF(ISERROR(MATCH(A2,$B$2:$B$10,0)),"",A2)

Note: In the above formula, A2 is the first cell of the column where you want to find the duplicates from; B2:B10 represents the data list you want to compare against.

Result:

As you can see, if the data in Column A exists in Column B, the value will be displayed; otherwise, the cells will be left blank.

Tips: This formula is case-insensitive.

Select duplicates in two columns with VBA code

This section will guide you through the steps to create a VBA code that identifies and selects duplicate values between two columns.

Step 1: Open the VBA module editor and copy the code

  1. Press Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
  2. In the opened window, click Insert > Module to create a new blank module.
  3. Then, copy and paste the below code into the blank module.
    VBA code: Find and select the duplicate values between two columns
    Sub Compare()
    'Update by Extendoffice
    Dim Range1 As Range, Range2 As Range, Rng1 As Range, Rng2 As Range, outRng As Range
    xTitleId = "KutoolsforExcel"
    On Error Resume Next
    Set Range1 = Application.Selection
    Set Range1 = Application.InputBox("Range1 :", xTitleId, Range1.Address, Type:=8)
    Set Range2 = Application.InputBox("Range2:", xTitleId, Type:=8)
    Application.ScreenUpdating = False
    For Each Rng1 In Range1
        xValue = Rng1.Value
        For Each Rng2 In Range2
            If xValue = Rng2.Value Then
                If outRng Is Nothing Then
                    Set outRng = Rng1
                Else
                    Set outRng = Application.Union(outRng, Rng1)
                End If
            End If
        Next
    Next
    outRng.Select
    Application.ScreenUpdating = True
    End Sub
    

Step 2: Execute this VBA code

  1. After pasting this code, please press F5 key to run this code. In the first prompt box, select the data list you want to select duplicates from. And then, click OK.
  2. In the second prompt box, select the data list you want to compare against, and then, click OK, see screenshot:

Result:

Now, the duplicate values from Columns A and B are selected in Column A, allowing you to fill the cells with color or copy and paste them as needed.

Tips:
  • This VBA code is case-sensitive;
  • If you want to select the duplicates from column B, you just need to swap the two selected columns when selecting data range.

Compare two columns for matches row-by-row

In Excel, comparing two columns row by row is often necessary to check for matches, which helps in tasks like checking records or analyzing data trends. Excel has different ways to do this, from easy formulas to special features, so you can choose the best one for your data's needs. Let’s take a look at some straightforward methods to get this job done effectively.

Compare two columns in the same row with formulas

Excel's formulas offer a straightforward yet potent approach for cross-column data comparison. Here’s how you can use them. Supposing, you have data in Column A and Column B, to check if the data in the two columns match, the following formulas may help you:

Tips: These formulas are versatile, not only applicable to text but also to numbers, dates, and times.
Using the Equal To operator (=):

Please enter or copy the following formula, press Enter key and then drag fill handle down to get all results. It will return TRUE if the values in the same row of Columns A and B are identical, and FALSE if they are not. See screenshot:

=A2=B2

IF Function:

If you want to make the comparison more informative, you could use the IF function to display custom messages.

Please enter or copy the formula below, press Enter key and then drag fill handle down to get all results. It will return Match when the values are the same and No Match when they are different. See screenshot:

=IF(A2=B2, "Match", "No Match")
Tips: You can change "Match", "No Match" to other expressions as you need.

EXACT Function:

If you need a case-sensitive comparison, the EXACT function is the way to go.

Please enter or copy the following formula, press Enter key and then drag fill handle down to get all results. It will return Match when the values are match exactly and No Match when they are different. See screenshot:

=IF(EXACT(A2,B2), "Match", "No match")   
Tips: You can change "Match", "No Match" to other expressions as you need.


Select and highlight matches in the same row with a useful tool - Kutools

If you need to select and shade the matches between two columns row by row instead of getting the result in a separate column, Kutools for Excel’s Compare Cells feature will be an excellent option. It enables you to swiftly select and apply a fill color to cells that match or differ in value within each row.

Note: If you want to use this Compare Cells feature, please download and install Kutools for Excel first.

Click Kutools > Compare Cells, in the Compare Cells dialog box, please do the following operations:

  1. Select the data from the two columns in the Find values in and According to boxes separately;
  2. Select Same cells under the Find section;
  3. Specify a background color for highlighting the matches under the Processing of results section;
  4. At last, click OK button.

Result:

Now, the matches in the same row are highlighted and selected in column A, making them available for you to copy and paste into any desired cells. See screenshot:

Tips:
  • This feature supports case-sensitive comparison if you check the Case sensitive option in the Compare Cells dialog box;
  • If you want to select the matches from column B, you just need to swap the two selected columns in the Find values in and According to boxes of the Compare Cells dialog box;
  • To apply this feature, please download and install Kutools for Excel first.

Compare two columns and highlight matches in the same row

Comparing two columns and highlighting matches in the same row can be efficiently done using Conditional Formatting in Excel. Here is a guide to identify and highlight row matches:

Step 1: Select the data range

Select the data range that you want to highlight the row matches.

Step 2: Apply the Conditional Formatting feature

  1. Click Home > Conditional Formatting > New rule. In the New Formatting Rule dialog box, please do the following operations:
    • 2.1 Select Use a formula to determine which cells to format from the Select a Rule Type list box;
    • 2.2 Type the below formula into the Format values where this formula is true text box;
      =$B2=$A2
    • 2.3 Then, click Format button.
  2. In the popped-out Format Cells dialog box, specify one color want to highlight the duplicate items. And click OK.
  3. When it returns the New Formatting Rule dialog box, click OK button.

Result:

Now, the matched values in the same row are highlighted at once, see screenshot:

Tips:
  • The formula in Conditional Formatting is not case sensitive.
  • If you aim to highlight cells with differing values, apply the following formula:
    =$B2<>$A2

Compare two columns and pull matching data

When you're dealing with two sets of data in Excel and need to find common items from one list in the other, lookup formulas are your go-to solution for retrieving these matches.

In Excel, if you have a list of fruits in Column A and their sales figures in Column B, now you want to match these with a selection of fruits in Column D to find their corresponding sales. How could you return the relative values from column B in Excel?

Please apply any one of the following formulas you need, then, drag the fill handle down to apply this formula to the rest cells.

  • All Excel versions:
    =VLOOKUP(D2, $A$2:$B$6, 2, FALSE)
  • Excel 365 and Excel 2021:
    =XLOOKUP(D2, $A$2:$A$6, $B$2:$B$6)

Result:

All the corresponding values will be displayed if a match is found, otherwise the #N/A error is returned, see screenshot:

Tips:
  • With above formulas, if there are fruits in Column D that don't have a match in Column A, they will return an error. To make these errors easier to understand, you can wrap your formula with the IFERROR function:
    • All Excel versions:
      =IFERROR(VLOOKUP(D2,$A$2:$B$10,2,FALSE), "No match found")
    • Excel 365 and Excel 2021:
      =IFERROR(XLOOKUP(D2, $A$2:$A$10, $B$2:$B$10),"No match found")
  • For those keen on advanced lookup formulas, Kutools for Excel provides an impressive suite of advanced lookup formulas that take the traditional VLOOKUP function to new heights, giving you unparalleled precision and efficiency in your data management tasks.

    Kutools for Excel boast a collection of more than 300 convenient tools designed to boost your productivity. Experience the full power with a free 30-day trial and elevate your spreadsheets today! Get it Now!

Related Articles:

  • Find and highlight duplicate rows in a range
  • Sometimes, there are may be some duplicate records in your data range of a worksheet, and now you want to find or highlight the duplicate rows in the range as following screenshots shown. Of course you can find them one after one by checking for the rows. But this is not a good choice if there are hundreds of rows. Here, I will talk about some useful ways for you to deal with this task.
  • Highlight duplicate values in different colors
  • In Excel, we can easily highlight the duplicate values in a column with one color by using the Conditional Formatting, but, sometimes, we need to highlight the duplicate values in different colors to recognize the duplicates quickly and easily as following screenshot shown. How could you solve this task in Excel?
  • Find, highlight, filter, count, delete duplicates in Excel
  • In Excel, duplicate data occurs time after time when we record data manually, copy data from other sources, or for other reasons. Sometimes, the duplicates are necessary and useful. However, sometimes the duplicate values lead to errors or misunderstanding. Here, this article will introduce methods to quickly identify, highlight, filter, count, delete duplicates by formulas, conditional formatting rules, third-party add-ins, etc. in Excel.
  • Remove duplicates and replace with blank cells
  • Normally when you apply Remove Duplicates command in Excel, it removes the entire duplicate rows. But sometimes, you want the blank cells to replace the duplicate values, in this situation, the Remove Duplicate command will not work. This article is going to guide you remove duplicates and replace them with blank cells in Excel.
Comments (48)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I'm trying to count duplicates from two columns (Column A is the name Column B is the address) how could I do it?
This comment was minimized by the moderator on the site
Hi, Committed, if you want to count total number of duplicates in two columns, firstly, use a formula to count the duplicates in first column:=COUNTIF(A2:A7, A2), A2:A7 is the range of the first column, A2 is the first data except header of the first column. Then use the same formula (change reference) to count the duplicates in second column. Finaly, use SUM function to get the total number of duplicates in two columns.Here is a tutorial which list almost all scenarios about comparing columns, if you are interested in this, you can visit:https://www.extendoffice.com/documents/excel/6392-excel-compare-two-columns.html</div>;
This comment was minimized by the moderator on the site
BUT I WANT DUPLICATE VALUE BEFORE ANY SPACING, IN COLUMN B WE FOUND DUPLICATE VALUES THAT'S GREAT BUT THERE IS SPACE I WANT THOSE VALUES WITHOUT SPACES, HOW CAN I?
This comment was minimized by the moderator on the site
Hi, just remove the spaces by using the Go to special function to find the space cell, then remove them by clicking Delete key after finding the duplicate values.
This comment was minimized by the moderator on the site
Здравствуйте. Макрос приведенный здесь выделяет дубликаты ячеек, но при попытке редактирования какой-либо ячейки выделение снимается сразу со всех дубликатов, как сделать что бы этого не происходило?
This comment was minimized by the moderator on the site
so lovely yeah!
This comment was minimized by the moderator on the site
I LOVE THIS SITE!
This comment was minimized by the moderator on the site
Thank You!
This comment was minimized by the moderator on the site
This shit don't work... just getting

outRng.Select object variable or with block variable not set??
This comment was minimized by the moderator on the site
great demo guys,keep rocking
This comment was minimized by the moderator on the site
Thank You Very Much Guys
This comment was minimized by the moderator on the site
that was really great.
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations