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.

Solved: Excel: Macro to match 2 tables and compute values

Discussion in 'Business Applications' started by rotvik, Jan 11, 2013.

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

    rotvik Thread Starter

    Joined:
    Jan 11, 2013
    Messages:
    30
    Hi

    I'm trying to make a macro that match table in sheet "Vocabulary" to the second table (columns Q,R,S,T) in sheet "Overview" and compute correct values in column U using weights from column F, sheet Vocabulary on values from a corresponding cell at column M, sheet Overview. The entries for every ISIN in the sheet Vocabulary are in no particular order and some positions from sheet Overview don't have to exist in Vocabulary at all (but I still need them to display in the table on the right to be able to make charts from summing these categories).

    How should the end result look like is displayed on the last sheet.:cool:

    Can anyone help?(y)


    Thanks

    Michael
     

    Attached Files:

  2. rotvik

    rotvik Thread Starter

    Joined:
    Jan 11, 2013
    Messages:
    30
    Any help would be appreciated.:)

    I can do some basic macros but this is way too complicated.
     
  3. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    If you don't need color, remove
    fillStart.CurrentRegion.Offset(1).Interior.ColorIndex = xlNone
    fillStart.Interior.Color = vbRed
     

    Attached Files:

  4. rotvik

    rotvik Thread Starter

    Joined:
    Jan 11, 2013
    Messages:
    30
    Thank you very much! It works like a charm and the code is so clean!

    And the highlighting of missing securities is a nice feature as well.

    One question: Will it work for more categories? There are up to 6 categories for every ISIN in Equity_Region, 5 categories for Bonds_Type and 4 categories for Bonds_Maturity.
     
  5. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    Yes. It check for the number of rows stated in the Vocab sheet to determine how many times to loop.
     
  6. rotvik

    rotvik Thread Starter

    Joined:
    Jan 11, 2013
    Messages:
    30
    That's great! Thanks.
     
  7. Sponsor

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/1084694

  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