Match excel spread sheet with macro

[Lillian Lian]

I have one excel spread sheet with sheet1 and sheet2, they both only have
columnA,

on sheet1 as following:
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)

on sheet2 as following:
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)

so sheet2 has (e-mail address removed), sheet1 does not have this.
so how to write the macro match this two sheet1 and sheet2 columnA and have
result (e-mail address removed) show up on sheet1

thanks
Lillian

 

[FSt1]

hi
this worked on your sample data. paste it in a standard module. back up your
data before runing it.
Sub twolists()
Dim r As Range
Dim rd As Range
Dim ro As Range
Dim rod As Range
Dim des As Range
Sheets("Sheet1").Activate
Columns("A:A").Sort Key1:=Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("Sheet2").Activate
Columns("A:A").Sort Key1:=Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("Sheet1").Activate
Set r = Sheets("sheet1").Range("A1")
Set ro = Sheets("sheet2").Range("A1")

Do While Not IsEmpty(ro)
Set rd = r.Offset(1, 0)
Set des = Sheets("sheet1").Range("A1"). _
End(xlDown).Offset(1, 0)
Set rod = ro.Offset(1, 0)
If r.Value <> ro.Value Then
des.Value = ro.Value
Set ro = rod
Else
Set r = rd
Set ro = rod
End If
Loop
MsgBox ("Done!")
End Sub

regards
FSt1

 

[Lillian Lian]

I run through this program is work, but the record is not correct,
on the sheet1...422 rows
on the sheet2...446 rows.
so combined two together I have 866 rows, but this alreay counting duplicate
record, I know the real counts about 500 records or more, but not 866 records.
how do you do that?

Thanks.

Lillian