1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

Discussion in 'Software Development' started by Mike83, Apr 25, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. Mike83

    Mike83 Thread Starter

    Joined:
    Apr 25, 2009
    Messages:
    1
    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!
     
  2. Sphinx

    Sphinx

    Joined:
    Aug 5, 2003
    Messages:
    606
    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.
     
  3. n777k

    n777k

    Joined:
    Apr 28, 2009
    Messages:
    19
    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
     
  4. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    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.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/821692

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice