Pivot Table Calculation

Hi,

It might be easier to explain by way of example. Name the data range, say 'source'. Save the file. Open a new file, via menu data take the pivot table option. Then external data. Choose the Excel file and then the source data. Continue and take the choice to edit in MS Query. Hit the 'SQL' button, edit the text so that it changes from something like to Enter the SQL, hit the 'open door' icon to exit MS Query, follow the wizard & complete the pivot table. It can now be moved back into the original file if you like.

Regards, Fazza

Fazza, thank you for the unexpected direction and innovative approach. I am inexperienced with MS Query, but am very interested. You have laid out a path for me to explore. I will post back results and potential questions of clarification.

Back sooner than I expected. I am using excel 2007. When I get to the "Choose Connection" for external data, I am not sure what to do.

Continue and take the choice to edit in MS Query. Hit the 'SQL' button, edit the text so that it changes from something like

Code:

SELECT event, A, B
FROM source

to

Code:

SELECT event, A, B, Iif(A=B,1,0) AS [Equal]
FROM source

Enter the SQL, hit the 'open door' icon to exit MS Query, follow the wizard & complete the pivot table. It can now be moved back into the original file if you like.

Click to expand...

I find the source and it takes directly to the Pivot Table. How do I get to MS Query? I'll keep digging. Thanks

Fazza, Many thanks. I got it to work following your lead. Now I need to rescale it to the real data, but I think it will be easy. In some of my searching, I have seen examples run from Sheet 2 rather than a new file. I'd appreciate your comments on that.

Creating the pivot table in a separate file is recommended. Once created, the resultant worksheet & pivot table can be moved into the original data file if desired. The problem with creating the pivot table in the same file as the data is 'memory leak'. Please refer http://support.microsoft.com/kb/319998

You might try creating the (external data source) pivot table in the same file as the data and see what happens. If there are problems then you know the solution: if not, then you are OK.

Some other comments. You can use a worksheet reference for the data - that is not a defined name. The syntax is [sheet name$]. I think more robust/reliable to use a defined name. Dynamic defined names are not usable.

Another significant thing to be aware of is 'mixed data types'. Such as http://www.dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-data-types/

Learning a little SQL can enormously increase the functionality.

As an option to pivot tables, there are query tables. They can be good in some situations.

As well as these manual approaches, it can all be done using VBA. VBA also offers additional approaches such as ADO with recordsets, SQL, etc.

Lots of good stuff. Have fun. Regards, Fazza

Many thanks. I understand your recommendations. I have scaled it to 9000 records and three SQL statements with a couple of additional calculated fields. I can see where it gives me some versatility over ranges with sumproduct and sumif statements.

Thanks again.