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 |
Combining data from 2 sheets - advanced help please
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 |
Combining data from 2 sheets - advanced help please
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 "Lars-Åke Aspelin" wrote in message ... 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 |
All times are GMT +1. The time now is 12:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com