Excel Comparison of 2 sheets

I have two sheets

sheet1 has col1, col2, col3, col4

and

Sheet2 has col1, col2, col3, col4

The data in both the sheets is sorted as col2, col1

I want to run a comparison on two sheets, I want find the records missing in sheet2 that are present in sheet 1

We need to comapre col2 and the col1

Example of data in sheet 1

col1, col2, col3, col4
a.b    d         1      1
d.c    d          2      1
E.F    D
w.s   e    
e.d    e           1     1


Example of sheet2
col1, col2, col3, col4
a.b    d         1      1
w.s   e    


FINAL SHEET1 SHOULD LOOK LIKE THIS
col1, col2, col3, col4  COL5
a.b    d         1      1
d.c    d          2      1     MISSING
E.F    d                         MISSING
w.s   e    
e.d    e           1     1    MISSING




if sheet1.col2row1=sheet2.col2ROW1
THEN CHECK TO SEE IF SHEET1.COL1.ROW1 (loop through sheet2.col1.rows till the col2 is same...)

No idea how to do it in excel.
SMadhaviAsked:
Who is Participating?
 
kgerbChief EngineerCommented:
Try this one.  Seems to work better.

Kyle
Sub CompareSheets()
Dim r As Range, c As Range, sAdd As String, bMissing As Boolean
For Each r In Range("B1", Cells(Rows.Count, "B").End(xlUp))
    bMissing = True
    With Worksheets("Sheet2").Range("B:B")
        Set c = .Find(r, LookIn:=xlValues)
            If Not c Is Nothing Then
                sAdd = c.Address
                Do
                    If c.Offset(, -1) = r.Offset(, -1) Then bMissing = False
                    Set c = .FindNext(c)
                Loop While Not c Is Nothing And c.Address <> sAdd
                If bMissing Then r.Offset(, 3) = "Missing"
           End If
    End With
Next r
End Sub

Open in new window

0
 
kgerbChief EngineerCommented:
SMadhavi,
Try this macro out.  I think it will do what you want.  If not let me know and we can change it.  Make a copy of your workbook and try it on the copy just to make sure nothing gets overwritten.

I pasted your sample data into Sheet1 and Sheet2 starting in cell A1 in both sheets.

Sub CompareSheets()
Dim r As Range, c As Range, sAdd As String
For Each r In Range("B1", Cells(Rows.Count, "B").End(xlUp))
    With Worksheets("Sheet2").Range("B:B")
        Set c = .Find(r, LookIn:=xlValues)
            If Not c Is Nothing Then
                sAdd = c.Address
                Do
                    If c.Offset(, -1) <> r.Offset(, -1) Then r.Offset(, 3) = "Missing"
                    Set c = .FindNext(c)
                Loop While Not c Is Nothing And c.Address <> sAdd
           End If
    End With
Next r
End Sub

Open in new window

Kyle
0
 
Saqib Husain, SyedEngineerCommented:
If VBA is not a must then this is a better way. Enter this formula in E1 or any other column and then copy it down.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
SMadhaviAuthor Commented:
tHE FIRST SUB JUST FINDS THE FIRST ALL OTHER VALUES SHOW AS MISSING
0
 
kgerbChief EngineerCommented:
hmmm....It worked for your test data.  Can you upload a sample file with more data so I can test it.

EDIT:
Never mind.  I see the problem.  I will have a new one shortly.
0
 
SMadhaviAuthor Commented:
Yes this works exactly the way I want. Thanks
0
 
kgerbChief EngineerCommented:
You're welcome.  Glad to help.

Kyle
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.