macro to compare info in another file

[Lynn]

Hi,
i have 2 excel files. file1 with a list of names and file2 with a list of
names and their age. Is it possible to write a macro whereby it will read
the name in file1 and serach for its age in file2?
thanks

 

[Frank Kabel]

Hi Lynn
a non macro solution could be the use of VLOOKUP.
Assumptions:
- column A in both workbooks contains the name
- column B in file 2 the age

try the following formula in B1 in the first file:
=VLOOKUP(A1,'C:\temp\[file2.xls]Sheet1'!$A$1:$B$1000,2,0)
and copy down (you have to adapt the path and filename as well as the
sheet name)

 

[Lynn]

how do i use VLOOKUP ?? and how can i automate VLOOKUP to check and
compare every single row of column A in both workbooks?

Hi Lynn
a non macro solution could be the use of VLOOKUP.
Assumptions:
- column A in both workbooks contains the name
- column B in file 2 the age

try the following formula in B1 in the first file:
=VLOOKUP(A1,'C:\temp\[file2.xls]Sheet1'!$A$1:$B$1000,2,0)
and copy down (you have to adapt the path and filename as well as the
sheet name)


--
Regards
Frank Kabel
Frankfurt, Germany

Hi,
i have 2 excel files. file1 with a list of names and file2 with a
list of names and their age. Is it possible to write a macro whereby
it will read the name in file1 and serach for its age in file2?
thanks

 

[Frank Kabel]

Hi
just enter this formula in B1 of your first file and copy this formula
down for all rows (assumption: column A stores your names)


--
Regards
Frank Kabel
Frankfurt, Germany

how do i use VLOOKUP ?? and how can i automate VLOOKUP to check and
compare every single row of column A in both workbooks?

Hi Lynn
a non macro solution could be the use of VLOOKUP.
Assumptions:
- column A in both workbooks contains the name
- column B in file 2 the age

try the following formula in B1 in the first file:
=VLOOKUP(A1,'C:\temp\[file2.xls]Sheet1'!$A$1:$B$1000,2,0)
and copy down (you have to adapt the path and filename as well as the
sheet name)


--
Regards
Frank Kabel
Frankfurt, Germany

Hi,
i have 2 excel files. file1 with a list of names and file2 with a
list of names and their age. Is it possible to write a macro whereby
it will read the name in file1 and serach for its age in file2?
thanks

 

[Lynn]

thanks.. it works finally...
where can i find more excel commands like vlookup ??

Hi
just enter this formula in B1 of your first file and copy this formula
down for all rows (assumption: column A stores your names)


--
Regards
Frank Kabel
Frankfurt, Germany

how do i use VLOOKUP ?? and how can i automate VLOOKUP to check and
compare every single row of column A in both workbooks?

Hi Lynn
a non macro solution could be the use of VLOOKUP.
Assumptions:
- column A in both workbooks contains the name
- column B in file 2 the age

try the following formula in B1 in the first file:
=VLOOKUP(A1,'C:\temp\[file2.xls]Sheet1'!$A$1:$B$1000,2,0)
and copy down (you have to adapt the path and filename as well as the
sheet name)


--
Regards
Frank Kabel
Frankfurt, Germany

Lynn wrote:
Hi,
i have 2 excel files. file1 with a list of names and file2 with a
list of names and their age. Is it possible to write a macro whereby
it will read the name in file1 and serach for its age in file2?
thanks

 

[Lynn]

btw, can i know what does !$A$1:$B$1000,2,0 in the formula means?

Hi
just enter this formula in B1 of your first file and copy this formula
down for all rows (assumption: column A stores your names)


--
Regards
Frank Kabel
Frankfurt, Germany

how do i use VLOOKUP ?? and how can i automate VLOOKUP to check and
compare every single row of column A in both workbooks?

Hi Lynn
a non macro solution could be the use of VLOOKUP.
Assumptions:
- column A in both workbooks contains the name
- column B in file 2 the age

try the following formula in B1 in the first file:
=VLOOKUP(A1,'C:\temp\[file2.xls]Sheet1'!$A$1:$B$1000,2,0)
and copy down (you have to adapt the path and filename as well as the
sheet name)


--
Regards
Frank Kabel
Frankfurt, Germany

Lynn wrote:
Hi,
i have 2 excel files. file1 with a list of names and file2 with a
list of names and their age. Is it possible to write a macro whereby
it will read the name in file1 and serach for its age in file2?
thanks

 

[Frank Kabel]

Hi
for VLOOKUP have a look at
http://www.mvps.org/dmcritchie/excel/vlookup.htm

in your case VLOOKUP searches in column A (from row 1 to row 1000) for
your value and returns the value from the second column (that is column
B)

--
Regards
Frank Kabel
Frankfurt, Germany

btw, can i know what does !$A$1:$B$1000,2,0 in the formula means?

Hi
just enter this formula in B1 of your first file and copy this formula
down for all rows (assumption: column A stores your names)


--
Regards
Frank Kabel
Frankfurt, Germany

how do i use VLOOKUP ?? and how can i automate VLOOKUP to check and
compare every single row of column A in both workbooks?

Hi Lynn
a non macro solution could be the use of VLOOKUP.
Assumptions:
- column A in both workbooks contains the name
- column B in file 2 the age

try the following formula in B1 in the first file:
=VLOOKUP(A1,'C:\temp\[file2.xls]Sheet1'!$A$1:$B$1000,2,0)
and copy down (you have to adapt the path and filename as well

as
the

sheet name)


--
Regards
Frank Kabel
Frankfurt, Germany

Lynn wrote:
Hi,
i have 2 excel files. file1 with a list of names and file2 with a
list of names and their age. Is it possible to write a macro whereby
it will read the name in file1 and serach for its age in file2?
thanks

 

[Lynn]

am i right to say:
!$A$1 -> means look in column A row 1 in file2
what does :$B$1000 -> means??
what does ,2,0 -> means??
sorry i am really new to this..

Hi
for VLOOKUP have a look at
http://www.mvps.org/dmcritchie/excel/vlookup.htm

in your case VLOOKUP searches in column A (from row 1 to row 1000) for
your value and returns the value from the second column (that is column
B)

--
Regards
Frank Kabel
Frankfurt, Germany

btw, can i know what does !$A$1:$B$1000,2,0 in the formula means?

Hi
just enter this formula in B1 of your first file and copy this formula
down for all rows (assumption: column A stores your names)


--
Regards
Frank Kabel
Frankfurt, Germany
how do i use VLOOKUP ?? and how can i automate VLOOKUP to check and
compare every single row of column A in both workbooks?

Hi Lynn
a non macro solution could be the use of VLOOKUP.
Assumptions:
- column A in both workbooks contains the name
- column B in file 2 the age

try the following formula in B1 in the first file:
=VLOOKUP(A1,'C:\temp\[file2.xls]Sheet1'!$A$1:$B$1000,2,0)
and copy down (you have to adapt the path and filename as well as
the
sheet name)


--
Regards
Frank Kabel
Frankfurt, Germany

Lynn wrote:
Hi,
i have 2 excel files. file1 with a list of names and file2 with a
list of names and their age. Is it possible to write a macro
whereby
it will read the name in file1 and serach for its age in file2?
thanks

 

[Frank Kabel]

Hi
the part $A$1:$B$1000 is the toal range in which VLOOKUP searches (so
from cell A1 down to cell B1000). VLOOKUP will always search in the
leftmost column
The part ',2' indicates that the second column of the range
$A$1:$B$1000 should be returned -> thats i column B
The part ',0' indicated that VLOOKUP should return an exact match

for further infos check the help and the link I provided to you

--
Regards
Frank Kabel
Frankfurt, Germany

am i right to say:
!$A$1 -> means look in column A row 1 in file2
what does :$B$1000 -> means??
what does ,2,0 -> means??
sorry i am really new to this..

Hi
for VLOOKUP have a look at
http://www.mvps.org/dmcritchie/excel/vlookup.htm

in your case VLOOKUP searches in column A (from row 1 to row 1000) for
your value and returns the value from the second column (that is column
B)

--
Regards
Frank Kabel
Frankfurt, Germany

btw, can i know what does !$A$1:$B$1000,2,0 in the formula means?

Hi
just enter this formula in B1 of your first file and copy this formula
down for all rows (assumption: column A stores your names)


--
Regards
Frank Kabel
Frankfurt, Germany
how do i use VLOOKUP ?? and how can i automate VLOOKUP to

check
and

compare every single row of column A in both workbooks?

Hi Lynn
a non macro solution could be the use of VLOOKUP.
Assumptions:
- column A in both workbooks contains the name
- column B in file 2 the age

try the following formula in B1 in the first file:
=VLOOKUP(A1,'C:\temp\[file2.xls]Sheet1'!$A$1:$B$1000,2,0)
and copy down (you have to adapt the path and filename as

well
as

the
sheet name)


--
Regards
Frank Kabel
Frankfurt, Germany

Lynn wrote:
Hi,
i have 2 excel files. file1 with a list of names and

file2
with a

list of names and their age. Is it possible to write a macro
whereby
it will read the name in file1 and serach for its age in file2?
thanks

 

[Lynn]

i find that the formula don't work when the names are in column C and age is
in column E of file2. While column A in file1 remains the same with the
names.
can i know why?

Hi
the part $A$1:$B$1000 is the toal range in which VLOOKUP searches (so
from cell A1 down to cell B1000). VLOOKUP will always search in the
leftmost column
The part ',2' indicates that the second column of the range
$A$1:$B$1000 should be returned -> thats i column B
The part ',0' indicated that VLOOKUP should return an exact match

for further infos check the help and the link I provided to you

--
Regards
Frank Kabel
Frankfurt, Germany

am i right to say:
!$A$1 -> means look in column A row 1 in file2
what does :$B$1000 -> means??
what does ,2,0 -> means??
sorry i am really new to this..

Hi
for VLOOKUP have a look at
http://www.mvps.org/dmcritchie/excel/vlookup.htm

in your case VLOOKUP searches in column A (from row 1 to row 1000) for
your value and returns the value from the second column (that is column
B)

--
Regards
Frank Kabel
Frankfurt, Germany
btw, can i know what does !$A$1:$B$1000,2,0 in the formula means?

Hi
just enter this formula in B1 of your first file and copy this
formula
down for all rows (assumption: column A stores your names)


--
Regards
Frank Kabel
Frankfurt, Germany
how do i use VLOOKUP ?? and how can i automate VLOOKUP to check
and
compare every single row of column A in both workbooks?

Hi Lynn
a non macro solution could be the use of VLOOKUP.
Assumptions:
- column A in both workbooks contains the name
- column B in file 2 the age

try the following formula in B1 in the first file:
=VLOOKUP(A1,'C:\temp\[file2.xls]Sheet1'!$A$1:$B$1000,2,0)
and copy down (you have to adapt the path and filename as well
as
the
sheet name)


--
Regards
Frank Kabel
Frankfurt, Germany

Lynn wrote:
Hi,
i have 2 excel files. file1 with a list of names and file2
with a
list of names and their age. Is it possible to write a macro
whereby
it will read the name in file1 and serach for its age in
file2?
thanks

 

[Frank Kabel]

Hi
change the formula to
=VLOOKUP(A1,'C:\temp\[file2.xls]Sheet1'!$C$1:$E$1000,3,0)


--
Regards
Frank Kabel
Frankfurt, Germany

i find that the formula don't work when the names are in column C and age is
in column E of file2. While column A in file1 remains the same with the
names.
can i know why?

Hi
the part $A$1:$B$1000 is the toal range in which VLOOKUP searches (so
from cell A1 down to cell B1000). VLOOKUP will always search in the
leftmost column
The part ',2' indicates that the second column of the range
$A$1:$B$1000 should be returned -> thats i column B
The part ',0' indicated that VLOOKUP should return an exact match

for further infos check the help and the link I provided to you

--
Regards
Frank Kabel
Frankfurt, Germany

am i right to say:
!$A$1 -> means look in column A row 1 in file2
what does :$B$1000 -> means??
what does ,2,0 -> means??
sorry i am really new to this..

Hi
for VLOOKUP have a look at
http://www.mvps.org/dmcritchie/excel/vlookup.htm

in your case VLOOKUP searches in column A (from row 1 to row

1000)
for

your value and returns the value from the second column (that

is
column

B)

--
Regards
Frank Kabel
Frankfurt, Germany
btw, can i know what does !$A$1:$B$1000,2,0 in the formula me ans?

Hi
just enter this formula in B1 of your first file and copy this
formula
down for all rows (assumption: column A stores your names)


--
Regards
Frank Kabel
Frankfurt, Germany
how do i use VLOOKUP ?? and how can i automate VLOOKUP

to
check

and
compare every single row of column A in both workbooks?

Hi Lynn
a non macro solution could be the use of VLOOKUP.
Assumptions:
- column A in both workbooks contains the name
- column B in file 2 the age

try the following formula in B1 in the first file:
=VLOOKUP(A1,'C:\temp\[file2.xls]Sheet1'!$A$1:$B$1000,2,0)
and copy down (you have to adapt the path and filename

as
well

as
the
sheet name)


--
Regards
Frank Kabel
Frankfurt, Germany

Lynn wrote:
Hi,
i have 2 excel files. file1 with a list of names and file2
with a
list of names and their age. Is it possible to write

a
macro

whereby
it will read the name in file1 and serach for its age in
file2?
thanks