Match excel spread sheet with macro
 

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

on sheet1 as following:




on sheet2 as following:





so sheet2 has , sheet1 does not have this.
so how to write the macro match this two sheet1 and sheet2 columnA and have
result show up on sheet1

thanks
Lillian

Match excel spread sheet with macro
 

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" wrote:

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

on sheet1 as following:




on sheet2 as following:





so sheet2 has , sheet1 does not have this.
so how to write the macro match this two sheet1 and sheet2 columnA and have
result show up on sheet1

thanks
Lillian

Match excel spread sheet with macro
 

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

"FSt1" wrote:

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" wrote:

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

on sheet1 as following:




on sheet2 as following:





so sheet2 has , sheet1 does not have this.
so how to write the macro match this two sheet1 and sheet2 columnA and have
result show up on sheet1

thanks
Lillian