On Tue, 22 Jul 2008 17:02:32 +1000, "Mark"
wrote:

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