Too many rows of data

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?

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

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

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:


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:


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.

Thanks!

I'll try that first thing tomorrow morning when I get back to the office!

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

Misca said:

!

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?

Click to expand...

Misca

Glad it worked

It should refresh without a problem.