Merging data from several Excel 2010 spreadhseets

B

Bob

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.
 
T

trip_to_tokyo

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
 
D

Dave Peterson

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.)
 
D

Dave Peterson

I didn't notice the version of excel in your subject line. You can use the
=iferror() function.
 
S

Saxman

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/

 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top