comparing two excel sheetsSolved/Closed

Question

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

venkat1926 · Accepted Answer

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

XLComparator · Answer

An other way to easily solve your problem :
http://www.xlcomparator.net/
It's free and no macro coding is necessary.

Jake · Answer

Thanks for the link...works like a charm

venkat1926 · Answer

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

kish1975 · Answer

Hi,

   This really working good. Thankyou very much

kish1975 · Answer

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

kish1975 · Answer

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

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

venkat1926 · Answer

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

Comparing two excel sheets

9 responses

Similar discussions

Subscribe To Our Newsletter!