combining different records w/ common ID#s
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?
Re: combining different records w/ common ID#s
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.
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.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Tags for this Thread
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1