+ Reply to Thread
Results 1 to 2 of 2

combining different records w/ common ID#s

  1. #1
    Registered User
    Join Date
    11-16-2009
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2003
    Posts
    1

    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?

    Thanks!

    ~Clark

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    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.

    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 the icon 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!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1