Excel VBA - Match 2 Columns and Delete duplicates on 2 sheets
<p>Bit of a tricky problem here, I think. Or I'm missing that one function that will simplify it all :) </p>

<p>I have a spreadsheet with 2 sheets. Jan and Feb. I only care about columns 1 and 2 for the comparison. Here are examples of what I need to happen.</p>

<pre><code> --- Jan ---
results       Date/Time     column 3    column 4
test          Date/Time1    column 3    column 4
another_row   Date/Time     column 3    column 4

 --- Feb ---
test          Date/Time1    column 3    column 4
test          Date/Time2    column 3    column 4
test          Date/Time3    column 3    column 4
another_row   Date/Time2    column 3    column 4
results       Date/Time2    column 3    column 4
</code></pre>

<p>Output expected - Duplicates deleted, but the Feb version of the singular columns remain</p>

<pre><code>test          Date/Time1    column 3    column 4
another_row   Date/Time2    column 3    column 4
results       Date/Time2    column 3    column 4
</code></pre>

<p>Feb will contain the exact same entry above plus 24 other duplicate rows with 'test' as column 1 and a different Date/Time for column 2.</p>

<p>I want to only keep the row values that are common between the two sheets. So the row in Jan is the one I want to keep in Feb, while deleting the 24 others. </p>

<p>So, for each row in the Jan sheet I need to search for the values of column 1 for a match in sheet Feb and if there is a match, compare column 2. If both are matching, I want to keep it. If not, delete it. </p>

<p>One other caveat, there are not duplicates of each value. So I only want to perform this removal if there are duplicates at all. Any unique, singular values, I want to keep. They may have different column 2 (time/date) but if the column 1 value is singular, it should stay.</p>

<p>Can this be done in VBA? </p>

<p>Here's my attempt to just find and delete the duplicates. I haven't even gotten to the unique value situation yet. This may not be my best approach, but it's my latest one. I attempted to set a flag to true/false and then if either flag was False, it should be deleted. Like I said, this doesn't handle my unique value requirement. But I was hoping to at least have it delete the 24 duplicates and keep the 1 value I need. </p>

<pre><code>Private Sub CommandButton1_Click()
Dim lRow As Long
Dim lRow2 As Long
Dim cell As Range
Dim cell2 As Range
Dim nameBool As Boolean
Dim originatedBool As Boolean
Dim rDel As Range

Sheets("Jan").Select
lRow = Range("A" &amp; Rows.Count).End(xlUp).Row
lRow2 = Range("B" &amp; Rows.Count).End(xlUp).Row

Range("A2").Select
Do Until IsEmpty(ActiveCell)

    For Each cell In Range("A2:A" &amp; lRow) 'Assuming you have a 1 row header
    If cell.Value = Sheets("Feb").Cells(cell.Row, "A") Then
        'Sheets("Feb").Cells(cell.Row, "A").ClearContents
        nameBool = True
    Else
        nameBool = False
    End If

Next cell

For Each cell2 In Range("B2:B" &amp; lRow2)
    If cell2.Value = Sheets("Feb").Cells(cell2.Row, "B") Then
        originatedBool = True
    Else
        originatedBool = False
    End If

Next cell2

If nameBool = False Or originatedBool = False Then
    'Debug.Print "Deleted"

End If

'rDel.EntireRow.Delete

ActiveCell.Offset(1, 0).Select
Loop

End Sub
</code></pre>