I’m in the process of migrating a database of client records from an old Access-based database program to a new database program.
The new program will only accept .csv import files. I can export those from the Access database.
Here’s my issue:
The Access database is broken up in such a way that client groups are assigned unique ID numbers. Those numbers are then tied to entries in various tables that contain all the contact info, account info, & personal information for every client group stored in the database, pooled together (so each entry in those tables is marked with the ID# of the client group it belongs to).
After exporting these tables of pooled data from Access, I need to use Excel to link all the different sets of data together using ID# assigned to each contact group.
I’m wondering if anyone can help me discover what is the easiest & most effective way of linking all of these sets of data together in Excel, using their common ID numbers (which are always found in a column titled “Client ID”).
Are there any Excel macros out there that do something similar & could be modified to solve this issue?
Thanks!
~Clark
You can customize macros for your data, of course. But it may not be necessary. Bringing in matching data from one sheet to another based on a matching criteria is awfully easy to do with simple worksheet formulas.
LOOKUP() - if the data being matched is sorted ascending already
VLOOKUP() - if the data being matched is to the left of the data being returned
INDEX/MATCH() - no restriction.
Here's a sheet showing a simple example of how these formulas are used to draw matching data from one data set into another. You should be able to use this.
INDEX-MATCH-Simple.xls
Once you have all the data in the sheet using an INDEX/MATCH, you highlight all the columns of formulas and do a Copy > Edit > PasteSpecial > Values to remove the formulas.
==========
Once you've figured out the INDEX/MATCH formulas, you could also make a macro that just inserts the formulas for you, then removes the formulas leaving the values.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks