CODEWITHSUNDEEP
excelvbaduplicates
Liron Golan
Liron Golan

Reputation: 19

Remove duplicates from column A based on existing values in column B using VBA

I need to input data in column A and column B and get the data that's in column A but not in column B written to column C.

Examples of what I need:

picture1

picture2

Upvotes: 1

Views: 473

Answers (2)

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

A slightly different and faster approach without looping through cells on the sheet would be this...

Private Sub CommandButton1_Click()
Dim x, y(), dict
Dim i As Long, j As Long
x = Range("A1").CurrentRegion
Set dict = CreateObject("Scripting.Dictionary")
Columns("C").ClearContents
For i = 1 To UBound(x, 1)
    dict.Item(x(i, 2)) = ""
Next i
j = 1
For i = 1 To UBound(x, 1)
    If Not dict.exists(x(i, 1)) Then
        ReDim Preserve y(1 To j)
        y(j) = x(i, 1)
        j = j + 1
    End If
Next i
Range("C1").Resize(UBound(y), 1) = Application.Transpose(y)
End Sub

Upvotes: 1

Kostas K.
Kostas K.

Reputation: 8518

Place this in the code file behind your sheet and change CommandButton1 to the name of your button.

Option Explicit

Private Sub CommandButton1_Click()

    Dim r As Range, matched_ As Variant, counter_ As Long

    'Loop in each cell in Column A
    For Each r In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        If Not IsEmpty(r) Then
            'Loop for a matching value in Column B
            matched_ = Application.Match(r.Value, Columns(2), 0)

            'If match not found, write the value in Column C
            If IsError(matched_) Then
                counter_ = counter_ + 1
                Range("C" & counter_) = r.Value
            End If
        End If
    Next r
End Sub

Upvotes: 0

Related Questions