Solved

Excel Comparison of 2 sheets

Posted on 2012-03-19
7
188 Views
Last Modified: 2012-03-19
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.
0
Comment
Question by:SMadhavi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 12

Expert Comment

by:kgerb
ID: 37738955
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37739016
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
 

Author Comment

by:SMadhavi
ID: 37739464
tHE FIRST SUB JUST FINDS THE FIRST ALL OTHER VALUES SHOW AS MISSING
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Expert Comment

by:kgerb
ID: 37739573
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
 
LVL 12

Accepted Solution

by:
kgerb earned 500 total points
ID: 37739624
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
 

Author Comment

by:SMadhavi
ID: 37739685
Yes this works exactly the way I want. Thanks
0
 
LVL 12

Expert Comment

by:kgerb
ID: 37739694
You're welcome.  Glad to help.

Kyle
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
converting excel into labels 6 37
Calculating Sales Tax 13 68
count values within multiple bands 7 35
Sort columns by the column header Excel 2016 VBA 5 14
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question