To compare two columns and remove the duplicate values, we have already posted a tutorial about how to solve this problem by ‘Conditional Formatting’ feature ‘Remove Duplicate’ rule. Actually, there are some other ways to solve it as well. If you are not familiar with ‘Conditional Formatting’ feature, you can also use a formula to compare columns and remove the duplicate values. In this article, we will introduce you how to apply IF, ISERROR and MATCH functions in a formula to compare data, I hope after reading this article, you will find the way to resolve your problem.

Precondition:

See screenshot below. We prepare two lists with fruits. As the two lists are simple, so we can see ‘Apple’ exists in both two columns obviously. But if the list is very long, we cannot compare them easily, we need to use formula to compare them.

1. Compare Two Columns and Remove Duplicates by Formula

Step 1: In B2 which is just between two columns, enter the formula

=IF(ISERROR(MATCH(A2,$C$2:$C$7,0)),"Not Duplicate","Duplicate")

Step 2: Press Enter to get value. Verify that we get ‘Duplicate’ in B2. That means ‘Apple’ in list1 also exists in list2.

Step 3: Drag the fill handle down till the end of the list.

Step 4: Now we already find out duplicate values, if you want to remove the duplicate value from list1, you can click B1, then click Data->Filter under Sort & Filter group.

The filter dropdown list is created.

Step 5: Click the small arrow button to load criteria, check on ‘Duplicate’, then click OK.

Duplicate value in list1 is displayed.

Step 6: Just remove duplicate value from filtered list1. Now there is no duplicate value now.

2. Compare Two Columns and Remove Duplicates using VBA Code

Let’s explore the second method, which involves using VBA code to compare two columns and remove duplicates. This method provides a more automated approach, allowing for customization and flexibility in handling duplicates.

Press ALT + F11 to open the VBA editor window.

In the VBA editor window, go to the “Insert” menu and select “Module“

Copy the provided VBA code.

Paste the code into the newly created module.

Sub RemoveDuplicates()
    Dim rng1 As Range, rng2 As Range, cell As Range
    Dim ws As Worksheet
    Dim dict As Object
    
    ' Set the active worksheet
    Set ws = ActiveSheet
    
    ' Prompt to select Range 1
    On Error Resume Next
    Set rng1 = Application.InputBox("Select the first range:", Type:=8)
    On Error GoTo 0
    
    ' Prompt to select Range 2
    If Not rng1 Is Nothing Then
        On Error Resume Next
        Set rng2 = Application.InputBox("Select the second range:", Type:=8)
        On Error GoTo 0
    End If
    
    ' Check if both ranges are selected
    If rng1 Is Nothing Or rng2 Is Nothing Then
        MsgBox "Both ranges must be selected.", vbExclamation
        Exit Sub
    End If
    
    ' Create a dictionary object
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' Loop through the second range and add unique values to dictionary
    For Each cell In rng2
        If Not dict.exists(cell.Value) Then
            dict.Add cell.Value, Nothing
        End If
    Next cell
    
    ' Loop through the first range and remove duplicates
    For Each cell In rng1
        If dict.exists(cell.Value) Then
            cell.ClearContents
        End If
    Next cell
    
    ' Clean up
    Set dict = Nothing
End Sub

Close the VBA editor window. Return to your Excel workbook.  Press ALT + F8 to open the “Macro” dialog box.

In the “Macro” dialog box, select “RemoveDuplicates” from the list of available macros.

 Click the “Run” button to execute the selected macro.

Select the first range, click Ok button, then Select the second range for comparison. 

the code will then remove duplicates from first range based on the values in the second range.

3. Video: Compare Two Columns and Remove Duplicates

This Excel video tutorial, we’ll explore two methods to compare two columns and remove duplicates. We’ll start by using a formula based approach, followed by utilizing VBA code to achieve the same task.

https://youtu.be/8A2ag5MRa2I

4. Related Functions

• 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])….