Created another spread sheet if SSN is not match
 

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

Created another spread sheet if SSN is not match
 

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

Created another spread sheet if SSN is not match
 

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

Created another spread sheet if SSN is not match
 

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