PC Review


Reply
Thread Tools Rate Thread

Merging data from several Excel 2010 spreadhseets

 
 
Bob
Guest
Posts: n/a
 
      4th Oct 2011
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.

 
Reply With Quote
 
 
 
 
trip_to_tokyo
Guest
Posts: n/a
 
      4th Oct 2011
On Oct 4, 4:24*pm, "Bob" <(E-Mail Removed)> 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/ex...249.aspx?CTT=1
 
Reply With Quote
 
 
 
 
Dave Peterson
Guest
Posts: n/a
 
      5th Oct 2011
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
 
Reply With Quote
 
Dave Peterson
Guest
Posts: n/a
 
      5th Oct 2011
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
 
Reply With Quote
 
Nick Vivian
Guest
Posts: n/a
 
      10th Oct 2011
This is what you need:
http://excelexperts.com/VBA-Tips-Merge-2-Data-Sets
- run it 10 times each time specifying the previous results file and
an unmerged file.

Nick
http://excelexperts.com
 
Reply With Quote
 
Saxman
Guest
Posts: n/a
 
      15th Oct 2011
On Tue, 04 Oct 2011 16:24:12 +0100, Bob <(E-Mail Removed)> 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/ActiveDa.../6/h7YjVMjHzDs
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Merging data from several Excel 2010 spreadhseets Bob Microsoft Excel Misc 5 15th Oct 2011 02:17 PM
Merging data from several Excel 2010 spreadhseets Bob Microsoft Excel New Users 5 15th Oct 2011 02:17 PM
Match data of two identical strings in two separate spreadhseets =?Utf-8?B?TWFoZW5kcmE=?= Microsoft Excel Misc 0 14th Sep 2005 11:40 PM
why do check boxes move when printing excel spreadhseets =?Utf-8?B?U2ltb24gSmVmZm9yZA==?= Microsoft Excel Misc 3 22nd Jun 2005 02:04 PM
Same Name Range for different spreadhseets in a workbook =?Utf-8?B?TWFyaw==?= Microsoft Excel Programming 3 14th Mar 2005 07:59 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 04:44 PM.