Comparing two excel sheets

Solved/Closed
sanjay - Jul 15, 2009 at 04:21 AM
 Mathews - Oct 10, 2010 at 04:53 AM
Hello,

i have two excel sheets, one sheet has 100 email ids, the other have 200 email ids, the scenario is I want to know the common email ids from both the sheets. how to compare and get the result.

Thanks
Sanjay
Related:

9 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jul 15, 2009 at 07:03 AM
The assumption are that the email addresses are in A2 down both in sheet 1 and sheet 2
sheet 1
email
a
d
g
k

sheet 2
email
a
s
d
f
g
h
j
k
l

the macro is (modify to suit you)
Sub test()
Dim rng As Range, c As Range, cfind As Range
On Error Resume Next
Worksheets("sheet3").Cells.Clear
With Worksheets("sheet1")
Set rng = Range(.Range("A2"), .Range("a2").End(xlDown))
For Each c In rng
With Worksheets("sheet2")
Set cfind = .Columns("A:A").Cells.Find _
    (what:=c.Value, lookat:=xlWhole)
If cfind Is Nothing Then GoTo line1
cfind.Copy Worksheets("sheet3").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End With 'sheet 2
line1:
Next c
application.cutcopymode=false 
End With 'sheet 1


End Sub


aftr running the macro the result will be in sheet 3 as follows


a
d
g
k
How can I change the VB so it does the same comparison across four worksheets?
kish1975 Posts 12 Registration date Wednesday March 3, 2010 Status Member Last seen March 22, 2010 7
Mar 3, 2010 at 03:02 AM
Hi,

Iam new to excel, Macro given by you is working fine for single column. But if I have two columns in worksheet1 and 1 column in worksheet2, I should get two columns in worksheet3. for example

worksheet1

x y
abc 10
pqr 20
zyx 30

worksheet2

x
abc
xyz
zyx
rst
tyu

output :

x y
abc 10
zyx 30

Requesting to sort out the problem ASAP
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 3, 2010 at 07:14 AM
macro modified (see the statement underline-that is the change)

Sub test()
Dim rng As Range, c As Range, cfind As Range
On Error Resume Next
Worksheets("sheet3").Cells.Clear
With Worksheets("sheet1")
Set rng = Range(.Range("A2"), .Range("a2").End(xlDown))
For Each c In rng
With Worksheets("sheet2")
Set cfind = .Columns("A:A").Cells.Find _
    (what:=c.Value, lookat:=xlWhole)
If cfind Is Nothing Then GoTo line1
c.EntireRow.Copy Worksheets("sheet3").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End With 'sheet 2
line1:
Next c
Application.CutCopyMode = False
End With 'sheet 1


End Sub



Your macro has been a great help to identify the matching records- thank you. I have another situation in which I'm trying to compare the same data between sheet 1 and sheet 2, but I want sheet 3 to pull any items are are NOT matches. How can I change this macro to copy the non-matches to sheet 3?

Thanks
@Vjayat1999
Do you get the solution? Even I want to pull NOT matching items.
Great work, Save a lot of time and have peace of mind.
Keep up good work buddy.
An other way to easily solve your problem :
http://www.xlcomparator.net/
It's free and no macro coding is necessary.
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 4, 2010 at 07:33 PM
macro modified. see the underlined statements

Sub test()
Dim rng As Range, c As Range, cfind As Range
On Error Resume Next
Worksheets("sheet3").Cells.Clear
With Worksheets("sheet1")
Set rng = Range(.Range("A2"), .Range("a2").End(xlDown))
For Each c In rng
With Worksheets("sheet2")
Set cfind = .Columns("A:A").Cells.Find _
    (what:=c.Value, lookat:=xlWhole)
If cfind Is Nothing Then GoTo line1
'c.EntireRow.Copy Worksheets("sheet3").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
c.Copy Worksheets("sheet3").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
c.Offset(0, 2).Copy Worksheets("sheet3").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)


End With 'sheet 2
line1:
Next c
Application.CutCopyMode = False
End With 'sheet 1


End Sub


 
kish1975 Posts 12 Registration date Wednesday March 3, 2010 Status Member Last seen March 22, 2010 7
Mar 4, 2010 at 02:39 AM
Hi,

One more scenario I have . If I have three columns in worksheet1 of file1(book1) and 1 column in worksheet2 of file2(book2), I should get only required columns in worksheet3 with column names to a new file(if possible) or with in any file.
for example

file1
worksheet1

x y z
abc 10 a
pqr 20 b
zyx 30 c

file2
worksheet2

x
abc
xyz
zyx
rst
tyu

to a new file
output :

x z
abc a
zyx c

Requesting to sort out the problem ASAP
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 7, 2010 at 07:23 PM
This thread is too long and confusing now;. I am not able to get the latest message. will you please start a new thread giving your requirement with examples. thank you
Thanks for the link...works like a charm
kish1975 Posts 12 Registration date Wednesday March 3, 2010 Status Member Last seen March 22, 2010 7
Mar 4, 2010 at 02:28 AM
Hi,

This really working good. Thankyou very much
kish1975 Posts 12 Registration date Wednesday March 3, 2010 Status Member Last seen March 22, 2010 7
Mar 4, 2010 at 02:33 AM
Hi,

Macro given by you is working, Thankyou very much . But if I have two columns in worksheet1 and 1 column in worksheet2, I should get only required columns in worksheet3 with column names. for example

worksheet1

x y z
abc 10 a
pqr 20 b
zyx 30 c

worksheet2

x
abc
xyz
zyx
rst
tyu

output :

x z
abc a
zyx c

Requesting to sort out the problem ASAP