# Need help with excel VBA - matching and then copying data from two sheets



## Mike83 (Apr 25, 2009)

Hello,

I need to sort extensive data and am facing the following problem:

There are two sheets: Sheet1 and Sheet2

Columns in Sheet 1 are the following:
company PERMNO number, dates, market capitalization (no data in this one)

Columns in Sheet 2 are the following:

company PERMNO number, ticker, dates, market capitalization

In Sheet2, there is a whole range of dates (between 1990 and 2004) for every single company, whereas in Sheet2 there is one or 3-4 dates for a every single company.

I would like to match the exact date/dates in Sheet1 in the range of dates in Sheet2 and then if they match, copy the corresponding cell value from the market capitalization column in Sheet2 to the empty market capitalization column in Sheet1. I also want that the company PERNO numbers match.

In short: if PERMNO numbers match, match the date/dates in Sheet1 within the range of dates for the same company in Sheet 2 and copy the market capitalization value to Sheet1.

Example:

Sheet1:
A B C
1. PERMNO DATES MARKETCAP
2. 13123 199803 
3. 13123 199904 
4. 65456 200005 
5. 44550 200104 
6. 44550 200211 
7. 44550 199601

Sheet2:
A B C
1. PERMNO DATES MARKETCAP
2. 13123 199803 8900000
3. 13123 199804 7767575
4. 13123 199805 7567657
..............
.............
99. 13123 200412 7999999
100. 65456 199803 4141241
101. 65456 199804 4145331
101. 65456 199805 4145551
............
............
200. 65456 200412 4458888

When we match the date from sheet1 - 199803 and 199904 within the range from Sheet2, we copy market cap values from Sheet2 to Sheet1. PERMNO from Sheet2 should match with PERMNO from Sheet1

Thank you very much for your help! Highly appreciated!


----------



## Sphinx (Aug 5, 2003)

Off the top of my head, I would think all you need to do is use the built-in "find" function. Look it up. Make sure sheet 2 is sorted before you use the find. 

For each PERMNO in sheet1, do the find function on sheet2. When it finds a match it returns the row of the FIRST match in sheet2. At this point, loop through the rows in sheet2, starting on the row that the find function returns, until you hit a PERMNO that is not the one you are looking for. This will work only if sheet2 is sorted. On each of those iterations you can check for the matching date, and on that match you can do the copy of the marketcap.

In VBA, you have to be very careful about which sheet you are referencing. So make sure to always put sheet1.cells(3,4) instead of just cells(3,4).

Hope this helps somewhat.


----------



## n777k (Apr 28, 2009)

Hi Mike
Sounds like you can do without a macro. 
There is a function called Vlookup, so I think the formular per your example would use a combination of IF function and Vlookup and look like this:=if(A2=Sheet2!A2,vlookup(B2,data,2,true),"") and drag it down.

In this formular, I have assumed the range name as "data" you can define a range name by pressing Ctl+F3.
Hope this helps 
Nick


----------



## MRdNk (Apr 7, 2007)

n777k said:


> you can define a range name by pressing Ctl+F3.


Brilliant, I love it when I learn new shortcuts. Thanks.
--------------

Use the following formula:
=SUM(IF(Sheet2!$A$2:$A$4=Sheet1!A2,Sheet2!$C$2:$C$4))

You need to enter it into the cell by using: SHIFT+CTRL+ENTER.
You can then drag it down Column C as far as is required.

Change the changes to the appropriate amount of rows:
$A$4 / $C$4 - 4 is the last row in the table.

*NB. This should be in the "Business Applications" section of the forums. *


----------

