(Solved) – vba excel compare 2 columns with condition for 3rd column


In sheet1 I have two columns:

enter image description here

In Sheet2 I have one column:

enter image description here

In Sheet3 I want to get the following result:

enter image description here

Arguments to get the data in Sheet3 are: Value of column A of Sheet2 equals a value in column A of Sheet1 (can be random row #) AND if it equals then value in column B of Sheet1 should be “a”.

I wrote the following:

Sub MatchColumnsCondition()

    Dim sht1, sht2, sht3 As Worksheet
    Dim lr1, lr2, lr3 As Long
    Dim chk1, chk2 As Variant
    Dim out3 As Range
    Dim dup As Boolean
    Dim i, j

    Set sht1 = ThisWorkbook.Worksheets("Sheet1") 'data to search in including condition
    Set sht2 = ThisWorkbook.Worksheets("Sheet2") 'data to search from
    Set sht3 = ThisWorkbook.Worksheets("Sheet3") 'output data

    lr1 = sht1.Cells(sht1.Rows.Count, "A").End(xlUp).Row
    lr2 = sht2.Cells(sht2.Rows.Count, "A").End(xlUp).Row
    lr3 = sht3.Cells(sht3.Rows.Count, "A").End(xlUp).Row

    Set chk1 = sht1.Range("A1:A" & lr1)
    Set chk2 = sht2.Range("A1:A" & lr2)
    Set out3 = sht3.Range("A1:A" & lr3)

    For i = LBound(chk1) To UBound(chk1)
           For j = LBound(chk2) To UBound(chk2)
            If chk1(i, 1) = chk2(j, 1) And chk1.Offset(, 1) = "a" Then
                sht3.Range("A" & lr3) = chk1(i, 1)
            End If
        Next j
    Next i

End Sub

but I keep running into errors with it, but I don’t know how to make it work properly.

Leave a Reply

Your email address will not be published. Required fields are marked *