Too many rows of data

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,760
Office Version
  1. 365
Platform
  1. Windows
Hi!

I'm stuck with too many rows (250 000 and counting) of data that I need to manipulate in a Pivot table. If I could use my Excel 2007 this would be no problem at all but I have to make it work in Excel 2003.

I tried to handle the problem with consolidated Pivot Table but I wasn't too happy with the results. Too few options and a lot of work to get rid of the "unnecessary" columns of data. I also tried combining 5 "normal" Pivot Tables with a macro to combine the data into one single "normal" table but if anybody needs to change the row/column fields in any way, the whole combination thing is going to fall apart.

Now I'm thinking if Access could be used as a help but the problem is I have no experience whatsoever about Access and the deadline is on Wednesday. And the whole thing should be done with macros so that it'll be as easy as possible for the users to add more rows data to the pivot tables.

I've understood that Pivot Tables are not limitted to any number of rows they can handle but the only limitation is the computer memory available.

What I'm after is a simple way to combine 5-6 full worksheets of data into a one "normal" Pivot Table. All the worksheets will have all the same columns (30 of them in each worksheet) and up to 55 000 rows of data on each worksheet.

Could anybody help me out by coming up with a way to solve this problem?
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Can you post some sample data and tell us what the final output should look like, what needs excluding, etc? Also, how's it stored? In a .csv file?
 
Upvote 0
Does the pivot table have to be in the same file as the actual data? You can use a query to populate the pivot cache but this will work best if the data is in a separate file (otherwise you can come up against memory leakes).
 
Upvote 0
The ideal solution would be to have the actual data in a separate file. Right now all the data is in one workbook where people add new rows as new shipments take place: The whole data is just shipment records taken place in two years time and the purpose of the Pivot Table is to be able to find out certain trends and see how things are developing over time.

The columns consist of shipment and delivery dates, zip codes, terminal + warehouse locations, pallets, actual weights, freight weights, shipment prices, customer names and addresses and so on (all the columns will be in the same order and have the same column names on the first row). If only I could get the data in pivot cache, it would be a breeze to take it from there with VBA but - as I said - I don't know how to get it into the cache. And how to update the cache when new shipments take place (=they take place daily but the file is to be updated on weekly/monthly bases).
 
Upvote 0
1. Create a new file and (assuming you are using xl2003 or earlier) go Data>Pivot Table and Pivot Chart Report.
2. On the dialog that will open select External Data>Next>hit Get Data button.
3. In the Database tab of the Choose Data Source dialog you should see an option to select Excel files*. Click OK and then navigate to your file (make sure the data file is closed when you are doing this ie not open in Excel).
4. When you have clicked on the file, you may get a dialog saying "Data source has no visible tables" - click OK this is completely normal. On the next dialog you should see an Options button - click this. This will open up a Table Options dialog. Make seure you check System Tables. Click OK.
5. You will now see you have several sheets where you can take data from, click on one of them and populate all the fields you need from the sheet. Click Next button several times until you see the option to Return Data to Microsoft Excel. You actually want to select "Edit Query in Microsoft Query" option. CLick OK. This will bring up MS Query and the returned data.
6. Click on the SQL button which is on one of the toolbars (towards the left hand side). This will bring up the SQL being used to generate the query. You should see something like this:

Code:
SELECT `Sheet1$`.Field1, `Sheet1$`.Field2, `Sheet1$`.Field3
FROM `Sheet1$` `Sheet1$`

Where Sheet1 is your sheet name that you are getting data from and Field1 is the name of the heading (which will obviously be different for you, depending on what you have named the header). Now, assuming you actually want to extract data from Sheet1, Sheet2 and Sheet3, amend the SQL to:

Code:
SELECT `Sheet1$`.Field1, `Sheet1$`.Field2, `Sheet1$`.Field3
FROM `Sheet1$` `Sheet1$`
UNION ALL
SELECT `Sheet2$`.Field1, `Sheet2$`.Field2, `Sheet2$`.Field3
 FROM `Sheet2$` `Sheet2$`
UNION ALL
SELECT `Sheet3$`.Field1, `Sheet3$`.Field2, `Sheet3$`.Field3
 FROM `Sheet3$` `Sheet3$`

Click OK. You will get a warning message stating "MS Query cannot represent query graphically" - click on thru it it and then go File>Return Data to Microsoft Excel.

You can then create your pivot table as normal.
 
Upvote 0
Thanks!

I'll try that first thing tomorrow morning when I get back to the office!
 
Upvote 0
Thank you very much, Richard!

Your solution works like a charm!

I haven't tried to update the orginal data (the records are up to date) yet but I believe it will update normally from the database when new recods are added to the existing pages?

Misca
 
Upvote 0
!

I haven't tried to update the orginal data (the records are up to date) yet but I believe it will update normally from the database when new recods are added to the existing pages?

Misca

Glad it worked :-)

It should refresh without a problem.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

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