Hi Lillian - Try this one...
--
Jay
---------------------------------------------------------------
Sub Lillian4()
'Looks up data for matched names, copies to sheet1
'Moves data from sheet2 to sheet1 for unmatched names
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim refrng As Range
Dim f As Range

Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")

Application.ScreenUpdating = False
'Take matching data from sheet2 and copy to sheet1
ws1.Activate
Range("A1").Select
Set refrng = ws2.Range("A1:A" & ws2.Rows.Count)
Do
If Not InStr(1, ActiveCell.Value, "name") Then
If Not refrng.Find(ActiveCell.Value, lookat:=xlWhole) Is Nothing Then
Set f = refrng.Find(ActiveCell.Value, lookat:=xlWhole)
Range(f.Offset(0, 1), f.Offset(0, 3)).Copy ActiveCell.Offset(0, 2)
End If
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell = ""

'Take unmatched records from sheet2 and copy to sheet1
ws2.Activate
Range("A1").Activate
Set refrng = ws1.Range("A1:A" & ws1.Rows.Count)
Do
If Not InStr(1, ActiveCell.Value, "name") Then
If refrng.Find(ActiveCell.Value, lookat:=xlWhole) Is Nothing Then
Set a = ws1.Range("A" & ws1.Rows.Count).End(xlUp).Offset(1, 0)
a.Value = ActiveCell.Value
a.Offset(0, 2) = ActiveCell.Offset(0, 1).Value
a.Offset(0, 3) = ActiveCell.Offset(0, 2).Value
a.Offset(0, 4) = ActiveCell.Offset(0, 3).Value
End If
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell = ""

Application.ScreenUpdating = True
ws1.Activate
Range("A1").Select

End Sub
-----------------------------------------------------------------

"Lillian" wrote:

Jay,

I think I am kind of confuse, here is what I would like
the excel spreed sheet is call test.xls

one sheet1 I have
Name type
aaa vvv
bbb xxx
ccc zzz

on sheet2 I have
Name IP SSN OS
aaa 10.1.1.1 xxxx xp
bbb 10.1.2.1 xxxx xp
ccc 10.1.3.1 xxxx xp
ddd 10.1.4.1 xxxx xp

now match sheet1 and sheet2 for name
if they are match will move IP, SSN, OS from sheet2 to sheet1 of ColumnC, D, E
if they are not match like sheet2 has ddd which is not on sheet1 then this
record of ddd need moved to sheet1

with all rows ddd,10.1.4.1, xxx, xp.

so the final sheet1 would look like this
Name type IP SSN OS
aaa vvv 10.1.1.1 xxxx xp
bbb xxx 10.1.2.1 xxxx xp
ccc zzz 10.1.3.1 xxxx xp
ddd 10.1.4.1 xxxx xp

thanks you so much for the help, I really appreciated it.

Lillian