comparing data in column [sheet1] with data in column [sheet2] and execute "yes" or "no"

[dragon_m0nsta]

hi there guys,
i need a help here..

i need to compare data in column a and b in sheet1 [m_cert check]

with

data in column b and c in sheet2 [m_cert]


and if the data is the same, it will execute "Yes" in column c of sheet1 [m_cert check]
if its not same, it will execute "No" in column c of sheet1 [m_cert check]

additional info,
the data in sheet2 [m_cert] will be updated daily and it will increase daily.

the data in sheet1 [m_cert check] is paste from another source before we do the check. but no worries, the data that is paste is just numbers and not from formula.

thank you in advance

i atached the sample of what i need in the result.Book2.xlsx

[Fotis1991]

Hi

One way.

In C14 and copy down.

=IF(SUMPRODUCT((m_cert!$B$14:$B$1000=A14)*(m_cert!$C$14:$C$1000=B14))>0,"YES","NO")

[dragon_m0nsta]

thank you for your solution.

but somehow, do we have any vba that could do this with just one click.

as the data in m_cert will be increasing daily and sometimes the data that need to compare could be up to more than 100

[nilem]

or

Sub ertert()
Dim x, i&
With Sheets("m_cert")
    x = .Range("B14:C" & .Cells(Rows.Count, 2).End(xlUp).Row).Value
End With

With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For i = 1 To UBound(x)
        .Item(x(i, 1) & "~" & x(i, 2)) = 1
    Next i

    With Sheets("m_cert check")
        x = .Range("A14:C" & .Cells(Rows.Count, 2).End(xlUp).Row).Value
    End With

    For i = 1 To UBound(x)
        If .Exists(x(i, 1) & "~" & x(i, 2)) Then x(i, 3) = "Yes" Else x(i, 3) = "No"
    Next i
End With

With Sheets("m_cert check")
    .Range("A14:C" & .Cells(Rows.Count, 2).End(xlUp).Row).Value = x
End With
End Sub

[Fotis1991]

We can do it, using dynamic ranges or something else but if you want VBA, then look to nilem suggestion.

[dragon_m0nsta]

nilem, thank you so much.

it does really works well!

thanks again.

thank you to fotis1991 too for helping.