Need codes to extract some data

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

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
 
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
Click to expand...

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!
 
Thanks troy! Shall give it go.

Regards,
Tom

troy@eXL said:
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
Click to expand...

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!
Click to expand...
 
Back
Top