Combining data from 2 sheets - advanced help please

[Mark]

I have 2 worksheets with 3000 rows by 350 columns of data - various firm
monthly data back to 1980

For example, first sheet is PBV1 and contains Price to book value (PBV). The
second sheet is Price1 and contains Price (P). I need to extract Book value
(BV). So I want to divide PBV by P to get the BV.

Problem is the data does not match exactly and there are many N/A#s.

For example:

sheet PBV1
A B C D
1 1/80 2/80 3/80 etc
2 ABC 3.1 3.1 3.2 etc
3 BCF 4.1 4.1 4.3 etc
4 CXF N/A# N/A# 2.1 etc

sheet Price1
A B C D
1 1/80 2/80 3/80 etc
2 ABC 1.1 1.2 1.1 etc
3 ABX 2.1 2.4 2.5 etc

So I need to find the firm that matches in column A, then if it does
calculate PBV/P across 350 columns.

Many thanks for any suggestions

Cheers, Mark

 

[Lars-Åke Aspelin]

I have 2 worksheets with 3000 rows by 350 columns of data - various firm
monthly data back to 1980

For example, first sheet is PBV1 and contains Price to book value (PBV). The
second sheet is Price1 and contains Price (P). I need to extract Book value
(BV). So I want to divide PBV by P to get the BV.

Problem is the data does not match exactly and there are many N/A#s.

For example:

sheet PBV1
A B C D
1 1/80 2/80 3/80 etc
2 ABC 3.1 3.1 3.2 etc
3 BCF 4.1 4.1 4.3 etc
4 CXF N/A# N/A# 2.1 etc

sheet Price1
A B C D
1 1/80 2/80 3/80 etc
2 ABC 1.1 1.2 1.1 etc
3 ABX 2.1 2.4 2.5 etc

So I need to find the firm that matches in column A, then if it does
calculate PBV/P across 350 columns.

Many thanks for any suggestions

Cheers, Mark

Try this:

In the new sheet (BV1) you put
in cell A2:

=PBV1!A2

in cell B2:

='PBV1'!B2/VLOOKUP($A2,Price1!$A2:B2;COLUMN(),0)

Fill cell B2 to the right to cover all your 350 columns and then fill
row 2 down to fill as many firms as you have in the PBV1 sheet.

Hope this helps / Lars-Åke

 

[Mark]

Many thanks Lars-Åke

It is very much appreciated.

It works just fine on my test files. But applying it to the big data file it
ran all night and then the computer jammed up. So I'm trying to figure out
other options.

Any suggestions on something that would require less grunt.

Many thanks, Mark