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

9 replies

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
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
17
How can I change the VB so it does the same comparison across four worksheets?
0
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
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
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



9
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
0
@Vjayat1999
Do you get the solution? Even I want to pull NOT matching items.
0
Great work, Save a lot of time and have peace of mind.
Keep up good work buddy.
0
An other way to easily solve your problem :
http://www.xlcomparator.net/
It's free and no macro coding is necessary.
6
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
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


 
5

Didn't find the answer you are looking for?

Ask a question
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
2
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
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
1
Thanks for the link...works like a charm
0
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
0
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
0