Pivot table 'views'

[Chartist]

I have a very complex data source (80 columns, thousands of rows) and need different types of Pivot tables for analysis. I have created around 20 Pivot tables in different sheets based on the same data. What I wanted to know was, is there something like a saved 'view' which I can select in the Pivot table to generate different pivots in the same sheet (with different settings of rows, columns, etc but based on the same data)? Currently, to view the other Pivot, I have to go over to the other sheet - or customise the current pivot which doesnt make sense.

Thanks

 

[Fazza]

I charge by the hour too. But it is healthy rate.

From your workbook that contains a sheet called "data" populated with the exact headings "reading date", "time" & "kW" and lots of data, go ALT-F11 to get to VBA editor. In a module, selectable from LHS, copy & paste in code. Position cursor in middle of code and hit F5. Voila?

 

[jeffreyweir]

I'll have a go at testing it now. Is the 2nd posting of you code the same as the first?

 

[Fazza]

Jeffrey,

I mistakenly posted the same old code just above - post #20? - instead of the new code. I have only just pasted over the old code with the new. Please check you have the newer code.

F

 

[jeffreyweir]

Unfortunatly I get a run time error '-2147217865 (80040e37)': the Microsoft Jet database engine could not find the object 'data$'. make sure the object exists and that you spell its name and the path name correctly'.

Your thoughts?

 

[Fazza]

1. The file must have been saved at some time. If not already saved, save it.

2. The file must contain a worksheet called "data"
and it must have the expected headings.

(Actually, step 2 is not essential, a defined name can be used instead without using this alternative worksheet reference.)

The code is written assuming it is in the same workbook as the source data and that this same file is active when the code is run. So, if you see a new workbook that the file created, delete it and go back to the data file - maybe selecting a cell somewhere to be sure that it is the active file.

 

[jeffreyweir]

Ahhh...I hadn't saved the workbook. Mate, that is bloody amazing. Works incredibly fast. How does it work?

 

[Fazza]

It uses database type functionality. And is awesome, isn't it?

And it could be pulling data instead from a closed file or Access database at about identical speed. Or even from 10 files at once and putting it altogether.

As I've coded it, ADO is used. A technology to handle this sort of thing. It uses SQL which is a standard for working with databases.

You don't need to use ADO. You can do this without code at all - in fact, no program code and no worksheet formulas!!!!

To briefly explain, go via menu path data, import external data, new database query. OK to Excel files and then browse for your file. There'll be a message about no visible ranges if you have not used a defined name (it doesn't work on dynamic named ranges.) but you can OK to the message and in the 'options' button select 'system tables' [=worksheet names] and continue. Go to MS Query when you can and then hit the SQL button. You need to replace what you see by the SQL below and then see the results on screen and after hitting the open door button return them to the worksheet. This is now refreshable like a pivot table. Data, refresh.

This is brief, I'm sorry. If you work with lots of data it is worth learning. This can do in an instant what otherwise requires code, or array formulas that can take > 1 hour.

TRANSFORM SUM(kW)
SELECT `reading date`
FROM [data$] A
GROUP BY `reading date`
PIVOT time

 

[Fazza]

Jeffrey,

Beer o'clock for me. Be back later.

And, apologies to Chartist. I really hijacked your thread, but you're not forgotten.

F

 

[jeffreyweir]

That is fantastic, and you are a true gentleman for sharing it. I am grinning from ear to ear.

I've just started reading a few SQL books, so this is very timely. Reason I'm heading down the SQL path is because excel was taking too long for these sorts of queries, the way I was using it.

But I didn't realize you could do this sort of stuff in excel using SQL. Am I right on that? What is the relationship between SQL and excel?

The other reason I thought I might need SQL and either MySQL or SQL server is that my datasets have been getting so big that they are something like 100MB, but much of that data is columns of info that I might not need at any particular time. So I thought that SQL should allow me to query the database, and just pull out the specific fields that I wanted to work on, while maintaining links with the data source in case data needs to be updated.

Do you recommend any particular books on using SQL for data analysis? And given what you've just shown me, is there any reason that I would need something like MySQL or SQL server running at all (apart from the ability to have a master set of data that is secure?)

I'd love any literature pointers or web references to help a young apprentice on his journey.

Once again, thanks.

 

[jeffreyweir]

Oh to be in Perth. It's so cold here across the ditch in Wellington that the beer has frozen over. But I'm sure I can thaw some out when you are next over here.