Merging data from several Excel 2010 spreadhseets

Discussion in 'Microsoft Excel Discussion' started by Bob, Oct 4, 2011.

  1. Bob

    Bob Guest

    OK, here's the situation...

    I've got a membership database which has been exported from an Access
    Database into a number of different Excel spreadsheets. And of course I
    don't have the original! It's split into about 10 spreadsheets and I need
    to create a single one by merging the data from the various sources.

    All the spreadsheets start with "Membership number" in column 1, then
    different data. The spreadsheets don't necessarily contain data for every
    membership number, and in some cases there are multiple entries for the same
    membership number. There are about 4000+ membership accounts, otherwise I'd
    just spend a while on the copy/paste buttons.

    Is there a way I can set up some sort of merge which will carry the various
    elements of data into the primary spreadsheet with all the data for each
    membership number spread across the cells on a single line?

    Thanks in advance.
     
    Bob, Oct 4, 2011
    #1
    1. Advertisements

  2. On Oct 4, 4:24 pm, "Bob" <> wrote:
    > OK, here's the situation...
    >
    > I've got a membership database which has been exported from an Access
    > Database into a number of different Excel spreadsheets.  And of course I
    > don't have the original!  It's split into about 10 spreadsheets and I need
    > to create a single one by merging the data from the various sources.
    >
    > All the spreadsheets start with "Membership number" in column 1, then
    > different data.  The spreadsheets don't necessarily contain data for every
    > membership number, and in some cases there are multiple entries for the same
    > membership number.  There are about 4000+ membership accounts, otherwise I'd
    > just spend a while on the copy/paste buttons.
    >
    > Is there a way I can set up some sort of merge which will carry the various
    > elements of data into the primary spreadsheet with all the data for each
    > membership number spread across the cells on a single line?
    >
    > Thanks in advance.


    Check this out:-
    http://office.microsoft.com/en-gb/e...in-multiple-worksheets-HP010095249.aspx?CTT=1
     
    trip_to_tokyo, Oct 4, 2011
    #2
    1. Advertisements

  3. I would create a new worksheet with just the key values in column A. (Those
    membership numbers are unique, right?)

    You can just copy|paste creating a giant list with duplicates in this column A.

    Then use Data|Advanced filter to get rid of the duplicates.
    Debra Dalgleish describes it here:
    http://contextures.com/xladvfilter01.html#FilterUR

    Then you could use the next 10 columns (B, C, ...) to use =vlookup()'s to
    retrieve the data from each of the other 10 worksheets.

    Depending on the version of excel, you can use =iferror() or
    =if(iserror(vlookup()),"",vlookup())

    Since you have lots of data, I think I'd do a single column at a time and verify
    that the results are correct.

    Then I'd convert that column to values and start on the next.

    (If you don't convert to values, then you could have about 4000 * 10 * 2
    vlookup() functions (depending on your version of excel) and that could slow
    excel to a crawl.)




    On 10/04/2011 10:24, Bob wrote:
    > OK, here's the situation...
    >
    > I've got a membership database which has been exported from an Access Database
    > into a number of different Excel spreadsheets. And of course I don't have the
    > original! It's split into about 10 spreadsheets and I need to create a single
    > one by merging the data from the various sources.
    >
    > All the spreadsheets start with "Membership number" in column 1, then different
    > data. The spreadsheets don't necessarily contain data for every membership
    > number, and in some cases there are multiple entries for the same membership
    > number. There are about 4000+ membership accounts, otherwise I'd just spend a
    > while on the copy/paste buttons.
    >
    > Is there a way I can set up some sort of merge which will carry the various
    > elements of data into the primary spreadsheet with all the data for each
    > membership number spread across the cells on a single line?
    >
    > Thanks in advance.


    --
    Dave Peterson
     
    Dave Peterson, Oct 5, 2011
    #3
  4. I didn't notice the version of excel in your subject line. You can use the
    =iferror() function.

    On 10/05/2011 07:00, Dave Peterson wrote:
    > I would create a new worksheet with just the key values in column A. (Those
    > membership numbers are unique, right?)
    >
    > You can just copy|paste creating a giant list with duplicates in this column A.
    >
    > Then use Data|Advanced filter to get rid of the duplicates.
    > Debra Dalgleish describes it here:
    > http://contextures.com/xladvfilter01.html#FilterUR
    >
    > Then you could use the next 10 columns (B, C, ...) to use =vlookup()'s to
    > retrieve the data from each of the other 10 worksheets.
    >
    > Depending on the version of excel, you can use =iferror() or
    > =if(iserror(vlookup()),"",vlookup())
    >
    > Since you have lots of data, I think I'd do a single column at a time and verify
    > that the results are correct.
    >
    > Then I'd convert that column to values and start on the next.
    >
    > (If you don't convert to values, then you could have about 4000 * 10 * 2
    > vlookup() functions (depending on your version of excel) and that could slow
    > excel to a crawl.)
    >
    >
    >
    >
    > On 10/04/2011 10:24, Bob wrote:
    >> OK, here's the situation...
    >>
    >> I've got a membership database which has been exported from an Access Database
    >> into a number of different Excel spreadsheets. And of course I don't have the
    >> original! It's split into about 10 spreadsheets and I need to create a single
    >> one by merging the data from the various sources.
    >>
    >> All the spreadsheets start with "Membership number" in column 1, then different
    >> data. The spreadsheets don't necessarily contain data for every membership
    >> number, and in some cases there are multiple entries for the same membership
    >> number. There are about 4000+ membership accounts, otherwise I'd just spend a
    >> while on the copy/paste buttons.
    >>
    >> Is there a way I can set up some sort of merge which will carry the various
    >> elements of data into the primary spreadsheet with all the data for each
    >> membership number spread across the cells on a single line?
    >>
    >> Thanks in advance.

    >


    --
    Dave Peterson
     
    Dave Peterson, Oct 5, 2011
    #4
  5. Bob

    Nick Vivian Guest

    Nick Vivian, Oct 10, 2011
    #5
  6. Bob

    Saxman Guest

    On Tue, 04 Oct 2011 16:24:12 +0100, Bob <> wrote:

    > OK, here's the situation...
    >
    > I've got a membership database which has been exported from an Access
    > Database into a number of different Excel spreadsheets. And of course I
    > don't have the original! It's split into about 10 spreadsheets and I
    > need to create a single one by merging the data from the various sources.
    >
    > All the spreadsheets start with "Membership number" in column 1, then
    > different data. The spreadsheets don't necessarily contain data for
    > every membership number, and in some cases there are multiple entries
    > for the same membership number. There are about 4000+ membership
    > accounts, otherwise I'd just spend a while on the copy/paste buttons.
    >
    > Is there a way I can set up some sort of merge which will carry the
    > various elements of data into the primary spreadsheet with all the data
    > for each membership number spread across the cells on a single line?
    >
    > Thanks in advance.



    I found this very easy, but it costs!

    http://www.informationactive.com/

    http://www.youtube.com/user/ActiveDataForExcel#p/u/6/h7YjVMjHzDs
     
    Saxman, Oct 15, 2011
    #6
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. STEFAN BEAN

    Merging several databases together

    STEFAN BEAN, Oct 22, 2003, in forum: Microsoft Excel Discussion
    Replies:
    1
    Views:
    189
    Dave Peterson
    Oct 23, 2003
  2. Lorinc C

    Excel data merging across excel files and matching fielddata

    Lorinc C, Nov 10, 2006, in forum: Microsoft Excel Discussion
    Replies:
    2
    Views:
    199
  3. clucom
    Replies:
    0
    Views:
    659
    clucom
    May 2, 2012
  4. Replies:
    0
    Views:
    656
  5. Replies:
    0
    Views:
    513
Loading...

Share This Page