Comparing two excel sheets [Solved/Closed]

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

9 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
789
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
17
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 4150 users have said thank you to us this month

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
4
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
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
789
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.
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
789
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


 
Posts
12
Registration date
Wednesday March 3, 2010
Status
Member
Last seen
March 22, 2010
4
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
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
789
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
Posts
12
Registration date
Wednesday March 3, 2010
Status
Member
Last seen
March 22, 2010
4
Hi,

This really working good. Thankyou very much
Posts
12
Registration date
Wednesday March 3, 2010
Status
Member
Last seen
March 22, 2010
4
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