Pivot Table Help

[Maverick494]

Okay, I am using 6 sheets (yes it has to stay across 6 sheets) to try and make a pivot table that shows me counts. There are two columns that I need to get data out of on each sheet, column 1 would be all the same name I am counting EG. DATACENTERNAME1; column 2 has different names DATACENTERROOMNAME1, 2, 3, etc. I need it to break the data out in the following manner

Column 1 - Total count from DATACENTERNAME1
Column 2 - count for DATACENTERROOMNAME1
Column 2 - count for DATACENTERROOMNAME2
Column 1 - total count from DATACENTERNAME2
Column 2 - count for DATACENTERROOMNAME1
Column 2 - count for DATACENTERROOMNAME2

I can get it to give me just column 1, but all the columns come up under "Columns" when I use ALT+D+P and consolidate multiple tables and I can't get it to subsort multiple columns. I tried choosing each column individually, but I get an error about using single rows for some reason.

Ideas?

 

[cyrilbrd]

Hi, what version of Excel are you using?

 

[Fazza]

hello.

The 'consolidate multiple tables' is of limited value. Instead, starting from a new file, ALT-D-P and choose 'external data source' at the first step.

I'll have a look for some old examples. Google should find plenty too.

regards

 

[Fazza]

this thread seems to be the most comprehensive : http://www.mrexcel.com/forum/excel-questions/315768-creating-pivot-table-multiple-sheets.html

Basically manual approach is something like ALT-D-P, external data source, OK, excel files, browse & OK to your file, if you get a message about no visible tables OK to that and then choose 'options' & 'system tables' (to then see worksheet names), choose a data table/worksheet & field/s, follow wizard to the end & then choose the edit in MS Query option. Via the SQL icon edit the SQL, OK to enter it, acknowledge any message about not being able to graphically represent, see the dataset, via the 'open door' icon exit MS Query & finish. Set up the pivot table as required. Can be automated or can be done differently to do a heckuva lot more.

regards

SQL will be like

SELECT fieldname1, fieldname2
FROM [YourSheet1Name$]
UNION ALL
SELECT fieldname1, fieldname2
FROM [YourSheet2Name$]
UNION ALL
SELECT fieldname1, fieldname2
FROM [YourSheet3Name$]
UNION ALL
SELECT fieldname1, fieldname2
FROM [YourSheet4Name$]
UNION ALL
SELECT fieldname1, fieldname2
FROM [YourSheet5Name$]
UNION ALL
SELECT fieldname1, fieldname2
FROM [YourSheet6Name$]

 

[cyrilbrd]

Fazza, based on your experience, what would be a advantage of using sql over built-in PowerPivot?

 

[Fazza]

hello, cyril

I am not familiar with Power Pivot, so can't advise on that, sorry. I understand Power Pivot is somehow better suited to large datasets and has a better interface.

Normal pivot tables can do a lot more than we read in normal forum questions & for me can handle all I ever need which is typically 50,000 records and basic summaries - SUM & COUNT functions. By joining multiple tables and a little SQL pivot tables (& query tables) can do wonders. Add a little VBA with some ADO and even more things. I don't have the need to manipulate existing pivot tables using VBA as I sometimes see in forum questions: in fact generally I think with appropriate data set up (maybe with more than one table) and pivot table customisation (SQL to define the dataset) the VBA can be avoided, or a simpler approach taken. But I'm getting away from your question.

Maybe the Power Pivot can do more of the fancier stuff without needing VBA? Or the user interface makes more complex tasks easier. But again, I don't know. Maybe start a new thread?

regards

 

[cyrilbrd]

Thanks Fazza. Link to new thread here. Would be happy to read more from you.

 

[Fazza]

After reading the new thread, I hope cyril I haven't answered the wrong question just above.

"what would be a advantage of using sql over built-in PowerPivot?"

The thread is Pivot Table Help.

I understood the question to be using SQL to customise pivot tables. So more sophisticated pivot tables, without going to PowerPivot. I find this handles all I ever need - which is generally simple things.

I'm now wonder if by SQL you are referrring to SQL Server database, and wanting to handle very large amounts of data.

I typically have 50,000 records and many fields. Say 100 to 200. That is 5,000,000 to 10,000,000 cells. And normal pivot tables handle these easily. Your new thread mentions 40,000,000 cells and I'd guess that should be OK in a pivot table. You indicate performance issues and perhaps hardware limititations. It might be best to clarify about the SQL and specific issues you're experiencing (in your power pivot question). I'll avoid replying to the other thread for now for a couple of reasons - I can't add much value there for one, and more importantly by leaving it unanswered it will attract more attention. For the same reason, you might want to wait before adding more info to that thread.

regards

 

[cyrilbrd]

I hope cyril I haven't answered the wrong question just above.

No your answer was what I was looking for, no worries there, actually I think your opinion may shed some light on my initial query.

The thread is Pivot Table Help.

Sadly there is no SQL forum here so "PowerPivot Questions" was what I deemed (maybe wrongly) being the closest as you wisely use SQL to power up a Pivot table, while Power Pivot does it without the need to enter SQL statement. (not in Pivot Table Help...)

I'm now wonder if by SQL you are referring to SQL Server database, and wanting to handle very large amounts of data.

yes indeed I am looking for new ways to handle large data. SQL Server will indeed be the tool placed at my disposal for such and I have absolutely no background in this discipline. I was thinking of ACCESS as well but haven't received yet the software. As you mentioned, I am working with growing data and due to the nature of my work am forced to keep an eye on the past (referring here to the data) as we need to validate all results based on past events. A typical table is about 50000 rows and 300 columns. I am really curious to see what is out there (within or not Microsoft) that may be capable of churning this type of data.

Dynamic dashboards and reports are easily created within Excel, and I do not wish to use solely Tableau mainly as to limit operational cost.

Thanks again.

 

[Fazza]

FWIW: my mention of the thread being "Pivot Table Help" was to lead in to my explanation that I thought your question was about pivot tables, as opposed to my concern (now known to be wrong, thank you) that it was about SQL Server. And, the PowerPivot sub-forum is a great place to ask the question.

I am not experienced with SQL Server though I expect it has ample capacity for your data requirements. I guess MS Access would handle the job too. Either way, as I see it, they could be just the back end data storage - so no great challenge/obstacle for you. It is the decision on the front end - Excel or Access (or whatever else) - that will need to match up your requirements/capabilities/etc. I get by with Access but am not expert, being mainly an Excel user. What I have found is that MS Access has powerful built-in functionality that is easy to harness: Excel can be programmed to do the same sort of thing but it requires so much custom programming work to do what Access does with very little customisation that for any serious task one would choose MS Access every time. Basically it depends on what you need to do. (And obviously considering the need in the coming years too, as the data grows & requirements increase. Cause they always seem to!)

Regards