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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
SMadhaviAuthor Commented:
tHE FIRST SUB JUST FINDS THE FIRST ALL OTHER VALUES SHOW AS MISSING
0
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SMadhaviAuthor Commented:
Yes this works exactly the way I want. Thanks
0
kgerbChief EngineerCommented:
You're welcome.  Glad to help.

Kyle
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.