Combining multiple spreadsheet files

B

buccijg

New Member
Joined
Dec 18, 2006
Messages
4
Hello,

I am trying to create a pivot table based on multiple spreadsheet files. Each file has the same column names. I would like to treat these spreadsheet files as one huge database of information. I will exceed the 65,000 row limit and need to access these multiple spreadsheets as an external data source.

Does anyone have any ideas on the best method to combine these multiple spreadsheet files so that I can use a pivot table to report on them?

Thanks for your help.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
Sure. I've posted on this a few times, so please excuse my briefness here. [Try searching for posts I've written containg text "UNION ALL". Though not the post from with 24 hours ago which is unrelated.] I prefer to use defined names - though they can not be dynamic - for each table of source data, then you can go via menu path data, import external data, new database query and edit the SQL to be of the form (as ALL your headers are the same)
Code: 
SELECT *
FROM file1.table1
UNION ALL
SELECT *
FROM file2.table2
UNION ALL
SELECT *
FROM file3.table3
after exiting MS Query, select the option to create a pivot table. NOT return the data to a worksheet - as you will have > 65,536 rows.

Of course, the SQL does allow you to consolidate the data if you don't truly need all the detail in your PT. Also apply filtering, etc within the SQL.

Another thought, I haven't tried, would be simply open MS Access and create tables that are linked to your source data files and combine the data in MS Access. Then within Excel you could run the PT from the MS Access data. This might be easier to manage?

HTH, Fazza
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Take screenshots & simultaneously capture the link, place it into an Excel file which is linked to a FileMaker/Claris db.
Replies
3
Views
389
Dan_W
D
L
  • Question Question
replace data for multiple sheets from source file to destination file based on back up(date,time)
Replies
1
Views
167
leap out
L
T
  • Locked
  • Question Question
VBA with lookup cell reference and Get multiple columns from Source sheet
Replies
1
Views
116
Fluff
Fluff
L
  • Question Question
Creating macro for nested calculations within calculations
Replies
2
Views
126
LizN
L
A
  • Question Question
Changing row number dynamically in link to another file
Replies
1
Views
201
AlphaFrog
AlphaFrog

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top