I have two excel spread sheet,on sheet1 column D has SSN,on sheet2 column D
has SSN, I need to do the match this two sheet1 and sheet2, if both SSN did
not match, then I need to create on sheet3, show all the different record.

How can I do that in macro?

Thanks.

Lillian

Sub ABCF()
Dim rng as Range, rng1 as Range, cell as Range
Dim rng3 as Range
Dim res as Variant
' sheet with longer list of SSN
set rng = Worksheets("Sheet1").Range("D1:D500")
' sheet with shorter list of SSN
set rng1 = Worksheets("Sheet2").Range("D1:D50")
for each cell in rng
res = Application.Match(cell,rng1,0)
if not iserror(res) then
cell.EntireRow.Hidden = True
else
cell.Entirerow.Hidden = False
end if
Next
set rng3 = worksheets("Sheet1").columns(1).specialcells(xlVis ible)
rng3.EntireRow.copy _
Destination:=Worksheets("sheet3").Range("A1")
rng.Parent.Rows.Hidden = False
End Sub

--
Regards,
Tom Ogilvy

"Lillian" wrote in message
...
I have two excel spread sheet,on sheet1 column D has SSN,on sheet2 column
D
has SSN, I need to do the match this two sheet1 and sheet2, if both SSN
did
not match, then I need to create on sheet3, show all the different record.

How can I do that in macro?

Thanks.

Lillian

Tom:

I run this report only show 3 records on Sheet3, and I look this 3
records's SSN # are on both Sheet1 and Sheet2, any idea?

I suppose get the records if both Sheet1 and Sheet2 SSN do not match. it
means, if 111-111-1111 on sheet1, but not on sheet2, so I need print out this
record on sheet3.

Thanks.

Lillian


"Tom Ogilvy" wrote:

Sub ABCF()
Dim rng as Range, rng1 as Range, cell as Range
Dim rng3 as Range
Dim res as Variant
' sheet with longer list of SSN
set rng = Worksheets("Sheet1").Range("D1:D500")
' sheet with shorter list of SSN
set rng1 = Worksheets("Sheet2").Range("D1:D50")
for each cell in rng
res = Application.Match(cell,rng1,0)
if not iserror(res) then
cell.EntireRow.Hidden = True
else
cell.Entirerow.Hidden = False
end if
Next
set rng3 = worksheets("Sheet1").columns(1).specialcells(xlVis ible)
rng3.EntireRow.copy _
Destination:=Worksheets("sheet3").Range("A1")
rng.Parent.Rows.Hidden = False
End Sub

--
Regards,
Tom Ogilvy

"Lillian" wrote in message
...
I have two excel spread sheet,on sheet1 column D has SSN,on sheet2 column
D
has SSN, I need to do the match this two sheet1 and sheet2, if both SSN
did
not match, then I need to create on sheet3, show all the different record.

How can I do that in macro?

Thanks.

Lillian

In contrast, I ran it on some test data and it worked perfectly for me.

You must be doing something wrong.

--
Regards,
Tom Ogilvy

"Lillian" wrote in message
...
Tom:

I run this report only show 3 records on Sheet3, and I look this 3
records's SSN # are on both Sheet1 and Sheet2, any idea?

I suppose get the records if both Sheet1 and Sheet2 SSN do not match. it
means, if 111-111-1111 on sheet1, but not on sheet2, so I need print out
this
record on sheet3.

Thanks.

Lillian


"Tom Ogilvy" wrote:

Sub ABCF()
Dim rng as Range, rng1 as Range, cell as Range
Dim rng3 as Range
Dim res as Variant
' sheet with longer list of SSN
set rng = Worksheets("Sheet1").Range("D1:D500")
' sheet with shorter list of SSN
set rng1 = Worksheets("Sheet2").Range("D1:D50")
for each cell in rng
res = Application.Match(cell,rng1,0)
if not iserror(res) then
cell.EntireRow.Hidden = True
else
cell.Entirerow.Hidden = False
end if
Next
set rng3 = worksheets("Sheet1").columns(1).specialcells(xlVis ible)
rng3.EntireRow.copy _
Destination:=Worksheets("sheet3").Range("A1")
rng.Parent.Rows.Hidden = False
End Sub

--
Regards,
Tom Ogilvy

"Lillian" wrote in message
...
I have two excel spread sheet,on sheet1 column D has SSN,on sheet2
column
D
has SSN, I need to do the match this two sheet1 and sheet2, if both
SSN
did
not match, then I need to create on sheet3, show all the different
record.

How can I do that in macro?

Thanks.

Lillian