This post will guide you how to merge two list without duplicates in Excel. How do I combine two columns of data with remove duplicates command or VBA Code in Excel.

1. Merge Tow List of Data with Remove Duplicates Command

Assuming that you have two list of data, and you want to merge them in the third list without duplicates in your worksheet. How to do it. You can use the Remove Duplicates command to remove duplicate values firstly, then copy one list into another list. Let’s see the following introduction.

Step1: select the first list of data, and press Ctrl + C keys on your keyboard.

Step2: select one cell on the bottom of the anther list of data, and press Ctrl + V to paste it. And keep to select the entire list of data.

Step3: go to DATA tab, click Remove Duplicates command under Data Tools group. And the Remove Duplicates Warning dialog will appear.

Step4: select the Continue with the current selection option in the Remove Duplicates Warning dialog box. And click Remove Duplicates button. And the Remove Duplicates dialog will open.

Step5: click Ok button. You should see that all duplicate values found and removed.

2. Merge Two List without Duplicates with VBA Code in Excel

You can use VBA Code to merge two lists without duplicates in Excel. The code can prompt the user to select the ranges containing the two lists and the destination cell for the merged list using the Application.InputBox method. Just do the following steps:

Step1: Press ALT + F11 to open the Visual Basic Editor.

Step2: In the Visual Basic Editor, go to Insert > Module to create a new module.

Step3: Copy and paste the code into the module. Save the module and close the Visual Basic Editor.

Sub MergeTwoLists_ExcelHow()
    Dim list1 As Range, list2 As Range, mergedList As Range, destCell As Range
    Dim values1 As Variant, values2 As Variant, mergedValues As Variant
    Dim i As Long, j As Long, n As Long, k As Long
    Dim isDuplicate As Boolean
    
    'Prompt the user to select the range for the first list
    Set list1 = Application.InputBox("Select the range for the first list:", Type:=8)
    
    'Prompt the user to select the range for the second list
    Set list2 = Application.InputBox("Select the range for the second list:", Type:=8)
    
    'Prompt the user to select the destination cell for the merged list
    Set destCell = Application.InputBox("Select the destination cell for the merged list:", Type:=8)
    
    'Get the values from the first list
    values1 = list1.Value
    
    'Get the values from the second list
    values2 = list2.Value
    
    'Combine the two lists into one array
    ReDim mergedValues(1 To UBound(values1) + UBound(values2), 1 To 1)
    For i = 1 To UBound(values1)
        mergedValues(i, 1) = values1(i, 1)
    Next i
    n = UBound(values1) + 1
    For i = 1 To UBound(values2)
        isDuplicate = False
        For j = 1 To UBound(values1)
            If values2(i, 1) = values1(j, 1) Then
                isDuplicate = True
                Exit For
            End If
        Next j
        If Not isDuplicate Then
            mergedValues(n, 1) = values2(i, 1)
            n = n + 1
        End If
    Next i
    
    'Remove duplicates from the merged list
    ReDim tempArray(1 To UBound(mergedValues), 1 To 1)
    k = 1
    For i = 1 To UBound(mergedValues)
        isDuplicate = False
        For j = i + 1 To UBound(mergedValues)
            If mergedValues(i, 1) = mergedValues(j, 1) Then
                isDuplicate = True
                Exit For
            End If
        Next j
        If Not isDuplicate Then
            tempArray(k, 1) = mergedValues(i, 1)
            k = k + 1
        End If
    Next i
    ReDim mergedValues(1 To k - 1, 1 To 1)
    For i = 1 To k - 1
        mergedValues(i, 1) = tempArray(i, 1)
    Next i
    
    'Set the range for the merged list
    Set mergedList = destCell.Resize(UBound(mergedValues), 1)
    
    'Copy the merged values to the merged list range
    mergedList.Value = mergedValues
End Sub

Step4: Press ALT + F8 to open the Macros dialog. Select the MergeTwoLists_ExcelHow macro and click Run.

Step5: select the range for the first list, then the range for the second list, and finally the cell where you want to place the merged list.

Step6: The merged list should now appear in the selected cell.

3. Video: Merge Two List without Duplicates with VBA Code in Excel

In this video, I will show you how to merge two lists without duplicates in Excel using the remove duplicates command and VBA code.