Combining data from 2 sheets - advanced help please
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
|