compare sheet1 complete row with sheet2 complete row and paste the result in sheet3 in d coloumn

compare sheet1 complete row with sheet2 complete row and paste the result in sheet3 in d coloumn
I have to do all this by vba
this highlighted colour is only for understanding purpose
Book3.xlsm
Avinash SinghAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please give this a try...

Sub CompareSheets()
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim lr As Long, lc As Long, i As Long
Dim x, y, z(), dict
Dim str As String

Application.ScreenUpdating = False

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Set ws3 = Sheets("Sheet3")

Set dict = CreateObject("Scripting.Dictionary")

lr = ws2.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lr
    lc = ws2.Cells(i, Columns.Count).End(xlToLeft).Column
    y = ws2.Range("A" & i, ws2.Cells(i, lc)).Value
    str = Join(Application.Index(y, 1, 0), ",")
    dict.Item(str) = ""
Next i

lr = ws1.Cells(Rows.Count, 1).End(xlUp).Row
ReDim z(1 To lr, 1 To 1)

For i = 1 To lr
    lc = ws1.Cells(i, Columns.Count).End(xlToLeft).Column
    x = ws1.Range("A" & i, ws1.Cells(i, lc)).Value
    str = Join(Application.Index(x, 1, 0), ",")
    If dict.exists(str) Then
        z(i, 1) = "True"
    Else
        z(i, 1) = "False"
    End If
Next i
ws3.Range("D1", ws3.Range("D1").End(xlDown)).Clear
ws3.Range("D1").Resize(lr, 1).Value = z
Application.ScreenUpdating = True
End Sub

Open in new window

0
 
Avinash SinghAuthor Commented:
Thnx Neeraj sir for ur great support and thnx alot for giving ur precious time to this post
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Avinash!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.