How to match rows that have 2 separately matching columns

Question

I have 4 columns, A through D. I need to find rows where column C is the same in each row, and column D is the same in each row. It would be best to have a True or False value placed in column E. VBA or a formula works, although I'd think something like this is do-able with a formula.

For example, I have the following:

Row 1 XX 123 XYZ
Row 2 XX 234 XYZ
Row 3 XX 234 YZX
Row 4 XX 234 YZX

In this example, Column E would be False for Rows 1 and 2 and True for Rows 3 and 4.

What if Row 1 matched Row 3 and Row 2 match Row 4? What should your output be then? — RBarryYoung

Siddharth Rout Siddharth Rout · Accepted Answer · 2012-07-13T19:09:15

Is this what you are trying?

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long, i As Long

    Set ws = Sheets("Sheet1")

    With ws
        lRow = .Range("C" & .Rows.Count).End(xlUp).Row

        For i = 1 To lRow
            If i = 1 Then
                If .Range("C" & i).Value = .Range("C" & i + 1).Value And _
                .Range("D" & i).Value = .Range("D" & i + 1).Value Then _
                .Range("E" & i).Value = "True" Else .Range("E" & i).Value = "False"
            Else
                If (.Range("C" & i).Value = .Range("C" & i + 1).Value And _
                .Range("D" & i).Value = .Range("D" & i + 1).Value) Or _
                (.Range("C" & i).Value = .Range("C" & i - 1).Value And _
                .Range("D" & i).Value = .Range("D" & i - 1).Value) Then _
                .Range("E" & i).Value = "True" Else .Range("E" & i).Value = "False"
            End If
        Next i
    End With
End Sub

SNAPSHOT

enter image description here

How to match rows that have 2 separately matching columns

1 Answers