extracting matching data

[esharah]

I have two different files : They contain the following headers
1.

Company ID
Company Name
Contact ID
ID2003
old id
Title
First Name
LastName
Job Title
Email
Tel No
Fax
Address1
Address2
City Postc
ode Country
Account Manager
Switchboard Telephone
Number
Preferred Language

and the other FILE2 contains

Status DMC
Company ID
DMC User ID
Company Address
ZIP/City Country
countrycode
Language
First Name
LastName
Phone
Phone adjusted
Number of Calls
Result detail Result
Wave
CO
CB
Out
Available to call
Availability for Wave 9
Email address
Contact by email
ONLY Adjustments region State

The two have fields "first name " and "last name" common.I need to fin
out from the file 1 , the corresponding "ID 2003" for the common "firs
name " and "last name" and write that into the file 2,for these como
entries I have to check if the email and phone numbers have the extac
matching and in case no highlight the same in the file 2 , with
different formatting....As I do not know much programming in the V
mode..I would like any body to help

 

[Guest]

I would use a nested for loop to scroll through your information. Although this can be time consuming, so if you have loads of data it would be better to use Access or SQL

For I = 1 to 1
firstname = workbooks("File1").worksheets("sheet1").range("A" & I).Valu
Lastname = workbooks("File1").worksheets("sheet1").range("A" & I).Valu
ID2003 = workbooks("File1").worksheets("sheet1").range("A" & I).Valu
For I2 = 1 to 1
if firstname = workbooks("File2").worksheets("sheet1").range("A" & I2).Value the
if lastname = workbooks("File2").worksheets("sheet1").range("A" & I2).Value the
workbooks("File2").worksheets("sheet1").range("A" & I2).Value = ID200
exit fo
end i
end i
next I
next

This code will need to be modified slightly: the column number, workbook names, worksheet names and the from and to values of the for loops. Change the from value to the same as the first row of data that is NOT the header. The to value should be the last line that contains data. If the to value will change from time to time simply set it to 19999 and add the bellow code into each loop (change any appropriate values

If workbooks("File1").worksheets("sheet1").range("A" & I).Value = "" the
exit fo
end i

Hope I have helped

regard

Russel