update Records reference to new file
 

hi
I want to update AGE field in file A from file b. i.e update AGE.

The number of records in file A and File B are not equal and may be
different value.

this is what i want
If there is any new name if file b add it in file a. For all other Name
which are in file a and file b, just take the new AGE from file B and
update in file A.

file a (it has 5 colomn)
Name Number Field Qty Age
John 7878 8989 234 32
Peter 7877 23 9 NA
Mike 3433 343 --
Shart 343 343
William 323 52 5

file b (it has only two colomn)

name AGE
John 34
Peter NA
Mike NA
Josh 43
ASA NA
MOM 11

any advice on it.

thanks

update Records reference to new file
 

Will need to use a combination of

COUNTIF() &
VLOOKUP()

in a Macro.

Ant

update Records reference to new file
 

This should do it

::::::::::::::::::::::::::::

Sub AddEntries()

Set qrange = Workbooks("File A").Sheets("Sheet1").Range("A:A")
Set prange = Workbooks("File B").Sheets("Sheet1").Range("A:b")
Sheets("Sheet1").Range("A2").Select

Do Until IsEmpty(ActiveCell)
On Error Resume Next
ActiveCell.Offset(0, 4).Value = _
Application.WorksheetFunction.VLookup(ActiveCell.V alue, prange, 2, 0)
ActiveCell.Offset(1, 0).Select
On Error GoTo 0
Loop


Windows("File B").Activate
Sheets("Sheet1").Range("A2").Select

Do Until IsEmpty(ActiveCell)
qname = ActiveCell.Value
qage = ActiveCell.Offset(0, 1).Value

If Application.WorksheetFunction.CountIf(qrange, qname) = 0 Then
Windows("File A").Activate
ActiveCell.Value = qname

ActiveCell.Offset(0, 4) = qage
ActiveCell.Offset(1, 0).Select

Windows("File B").Activate
End If
ActiveCell.Offset(1, 0).Select

Loop

End Sub


:::::::::::::::::::::::::::::::::::::::

update Records reference to new file
 

This should do it

::::::::::::::::::::::::::::

Sub AddEntries()

Set qrange = Workbooks("File A").Sheets("Sheet1").Range("A:A")
Set prange = Workbooks("File B").Sheets("Sheet1").Range("A:b")
Sheets("Sheet1").Range("A2").Select

Do Until IsEmpty(ActiveCell)
On Error Resume Next
ActiveCell.Offset(0, 4).Value = _
Application.WorksheetFunction.VLookup(ActiveCell.V alue, prange, 2, 0)
ActiveCell.Offset(1, 0).Select
On Error GoTo 0
Loop


Windows("File B").Activate
Sheets("Sheet1").Range("A2").Select

Do Until IsEmpty(ActiveCell)
qname = ActiveCell.Value
qage = ActiveCell.Offset(0, 1).Value

If Application.WorksheetFunction.CountIf(qrange, qname) = 0 Then
Windows("File A").Activate
ActiveCell.Value = qname

ActiveCell.Offset(0, 4) = qage
ActiveCell.Offset(1, 0).Select

Windows("File B").Activate
End If
ActiveCell.Offset(1, 0).Select

Loop

End Sub


:::::::::::::::::::::::::::::::::::::::