update Records reference to new file

[tarone]

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

 

[somethinglikeant]

Will need to use a combination of

COUNTIF() &
VLOOKUP()

in a Macro.

Ant

 

[somethinglikeant]

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.Value, 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


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

 

[somethinglikeant]

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.Value, 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


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