Import matching data

[nmod]

Hello,

I am sure this is a very simple task, but I am just not finding the solution. I am trying to match unique id's (CUSIPs) from two seperate workbooks and pull the information (monthly payments) from one workbook into another. When this information is pulled over I also need to know which id's it was pulled for. I currently just color the id's when i copy and paste them from one sheet to another. I spend 10 hours or so every single month doing this and would like to cut it down to just a couple. Thank you in advance for your help.

 

[Joe4]

Welcome to the Board!

If you put your data into separate sheets within the same workbook file, you can use VLOOKUP function to pull the data you need (see Excel's built-in help for details and examples on the VLOOKUP function).

However, all things being equal, these seems like a task that could be done much more efficiently in a relational database program like Micorsoft Access (as what you are really talking about is a "relational" model, two files that are linked to one another by some linking field).

 

[nmod]

Thanks for the tip Joe. I found the VLOOKUP info on the site and I think that will be the way that I need to go. Good to know that i would have to move/copy the sheet into my workbook. Unfortunately this would need to be done each month on every servicer (8-10). I am not sure if this will save me that much time, but I am going to try it out and see. Could I link the sheet to the workbook or will it not work since there are macros on the workbook that convert it from the raw data the servicers send me to usable data.

I just started working with municipal bonds about 6 months ago so I am kinda stuck with the existing system for now but maybe I can propose a change over to access. Any links about switching it over to access from excel.

 

[Joe4]

I believe you can use VLOOKUPs across files, but you may need to have both files open at the time in order to get them to work.

I really don't know of any books of moving from Excel to Access. It isn't like Access is an extension of Excel, it is a whole different concept altogether. Access is a "relational database program", while Excel is a spreadsheet program. Different, by nature.

I would recommend getting some good introductory books (or classes) on Access, and be sure to read up on relational databases and the rules of normalization. Here is a good link on the topic: http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx