Need codes to extract some data
 

File 1 is a daily output of stock information and is large:
CODE DATE OPEN HIGH LOW CLOSE VOLUME
ANI 20071003 5.87 5.87 5.87 5.87 5740000
AIOD 20071003 6.26 6.26 6.26 6.26 428700
AIO 20071003 6.68 6.68 6.68 6.68 2953000
---- ----------- ---- ---- ---- ----
----
ECU 20071003 0.024 0.025 0.024 0.024 212200
EDEO 20071003 0.245 0.26 0.245 0.26 39667
EDS 20071003 0.62 0.62 0.62 0.62 14500
---- ----------- ---- ---- ---- ----
----
IOD 20071003 2.48 2.52 2.35 2.45 472000
IOF 20071003 1.8 1.82 1.795 1.815 7093641
IOH 20071003 0.72 0.72 0.665 0.7 129936
---- ----------- ---- ---- ---- ----
----
ZRL 20071003 0.49 0.49 0.48 0.48 61667
ZYL 20071003 0.051 0.054 0.05 0.052 753700

File 2 consists of my list of selected stock codes, where the CLOSE and
VOLUME data is needed to fill the blanks.
CODE CLOSE VOLUME
BOC
FLO
IOD
----
SED

Is there a way to do that? I much appreciate any help.

Regards,
Tom

Need codes to extract some data
 

On Oct 4, 11:38 am, "Tom" wrote:
File 1 is a daily output of stock information and is large:
CODE DATE OPEN HIGH LOW CLOSE VOLUME
ANI 20071003 5.87 5.87 5.87 5.87 5740000
AIOD 20071003 6.26 6.26 6.26 6.26 428700
AIO 20071003 6.68 6.68 6.68 6.68 2953000
---- ----------- ---- ---- ---- ----
----
ECU 20071003 0.024 0.025 0.024 0.024 212200
EDEO 20071003 0.245 0.26 0.245 0.26 39667
EDS 20071003 0.62 0.62 0.62 0.62 14500
---- ----------- ---- ---- ---- ----
----
IOD 20071003 2.48 2.52 2.35 2.45 472000
IOF 20071003 1.8 1.82 1.795 1.815 7093641
IOH 20071003 0.72 0.72 0.665 0.7 129936
---- ----------- ---- ---- ---- ----
----
ZRL 20071003 0.49 0.49 0.48 0.48 61667
ZYL 20071003 0.051 0.054 0.05 0.052 753700

File 2 consists of my list of selected stock codes, where the CLOSE and
VOLUME data is needed to fill the blanks.
CODE CLOSE VOLUME
BOC
FLO
IOD
----
SED

Is there a way to do that? I much appreciate any help.

Regards,
Tom

Hi Tom,

Use a =VLOOKUP() formula as follows:

in cell B2 enter =VLOOKUP($A2,'[File1.xls]Sheet1'!$A$1:$G
$1000,6,FALSE) for CLOSE (it's the 6th column in the lookup table)
in cell C2 enter =VLOOKUP($A2,[File1.xls]Sheet1'!$A$1:$G$1000,7,FALSE)
for VOLUME (it's the 7th column in the lookup table)
then copy these down for all your stock codes

HTH

cheers,
t.

Need codes to extract some data
 

Thanks troy! Shall give it go.

Regards,
Tom

"troy@eXL" wrote in message
oups.com...
On Oct 4, 11:38 am, "Tom" wrote:
File 1 is a daily output of stock information and is large:
CODE DATE OPEN HIGH LOW CLOSE VOLUME
ANI 20071003 5.87 5.87 5.87 5.87
5740000
AIOD 20071003 6.26 6.26 6.26 6.26 428700
AIO 20071003 6.68 6.68 6.68 6.68
2953000
---- ----------- ---- ---- ---- ----
----
ECU 20071003 0.024 0.025 0.024 0.024 212200
EDEO 20071003 0.245 0.26 0.245 0.26 39667
EDS 20071003 0.62 0.62 0.62 0.62 14500
---- ----------- ---- ---- ---- ----
----
IOD 20071003 2.48 2.52 2.35 2.45
472000
IOF 20071003 1.8 1.82 1.795 1.815
7093641
IOH 20071003 0.72 0.72 0.665 0.7 129936
---- ----------- ---- ---- ---- ----
----
ZRL 20071003 0.49 0.49 0.48 0.48 61667
ZYL 20071003 0.051 0.054 0.05 0.052 753700

File 2 consists of my list of selected stock codes, where the CLOSE and
VOLUME data is needed to fill the blanks.
CODE CLOSE VOLUME
BOC
FLO
IOD
----
SED

Is there a way to do that? I much appreciate any help.

Regards,
Tom

Hi Tom,

Use a =VLOOKUP() formula as follows:

in cell B2 enter =VLOOKUP($A2,'[File1.xls]Sheet1'!$A$1:$G
$1000,6,FALSE) for CLOSE (it's the 6th column in the lookup table)
in cell C2 enter =VLOOKUP($A2,[File1.xls]Sheet1'!$A$1:$G$1000,7,FALSE)
for VOLUME (it's the 7th column in the lookup table)
then copy these down for all your stock codes

HTH

cheers,
t.

www.eXtreme-eXcel.com
...be indispensable... they'll pay you more!