Compare two column data with another two columns in another sheet

[uhd04]

Hi,

i'm trying to write a vba macro to compare data of two cells in same row with two cells in another sheet in same workbook.

For example below is my data

Sheet1

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C (Exists or not)[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]100[/TD]
[TD]200[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]xyz[/TD]
[TD]abc[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]fff[/TD]
[TD]ggg[/TD]
[TD]no[/TD]
[/TR]
</tbody>[/TABLE]


So i want to compare ROW1 (Cell A1 AND B1) from Sheet1 with data in Sheet2 and if it exists then mark yes in next column in Sheet1. For Example:

Sheet2

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]xyz[/TD]
[TD]abc[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]100[/TD]
[TD]200[/TD]
[/TR]
</tbody>[/TABLE]

Here's how i've got my code so far.

Public Sub compare()

With Sheets("Sheet1")
Dim wk As Worksheet, lr As Long, i&, lr2 As Long, j&


    Set sh1 = Sheets("Sheet1"): Set sh2 = Sheets("Sheet2")


'Application.ScreenUpdating = 0
With Sheets("Sheet1")


    lr = .Range("A" & Rows.Count).End(xlUp).Row
   lr2 = .Range("B" & Rows.Count).End(xlUp).Row

             '** Not sure how i should write the If and For statement further. I tried a few things but it didnt work as i wanted. What i used to compare two cell is 

                  'If IsNumeric(Application.Match(.Range("A" & i).Value, Sheets("Sheet2").Columns("B"), 0)) then
                  further code....


End Sub

Thanks everyone for your reply.

 

[Sixthsense]

Sub CheckAvailability()
Dim rMyRng As Range, rCompare As Range, r As Range, lFound As Long, blStatus As Boolean

Application.ScreenUpdating = False

With Sheets("Sheet1")
    Set rMyRng = .Range("A1:B" & Range("B" & Rows.Count).End(xlUp).Row)
End With

With Sheets("Sheet2")
    Set rCompare = .Range("A1:B" & Range("B" & Rows.Count).End(xlUp).Row)
End With

For Each r In rMyRng.Rows
    With r
        .Select
        blStatus = False
        lFound = Application.CountIfs(rCompare.Columns(1), .Cells(1).Value, rCompare.Columns(2), .Cells(2).Value)
        If lFound Then blStatus = True
        .Cells(2).Offset(, 1).Value = blStatus
    End With
Next r
        
Application.ScreenUpdating = True
        
End Sub

 

[Peter_SSs]

I'm assuming both sheets have headings in row 1 with data staring in row 2

Firstly, do you really need a macro? This formula in C2 of Sheet1 (copied down) should give you the results you want.

=IF(COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,B2),"Yes","No")

If a macro is required, try this.

Sub Compare_Columns()
  With Sheets("Sheet1")
    With .Range("C2:C" & .Range("A" & .Rows.Count).End(xlUp).Row)
      .FormulaR1C1 = "=IF(COUNTIFS(Sheet2!C[-2],RC[-2],Sheet2!C[-1],RC[-1]),""Yes"",""No"")"
      .Value = .Value
    End With
  End With
End Sub

 

[uhd04]

Thanks guys. Both worked as i needed.

 

[nkiouari]

Sounds great!

How do I do if the content of cells differs from one sheet to another. For example, if I have in sheet 1 the range 105 - 199 to check in sheet 2 range 100 - 200

 

[Peter_SSs]

Sounds great!

How do I do if the content of cells differs from one sheet to another. For example, if I have in sheet 1 the range 105 - 199 to check in sheet 2 range 100 - 200

Formula or macro?

 

[jason72670]

is there a way to do this with a formula rather than a macro?