Pivot Table pain, Anything they're not suited for?

[srj19]

So pivot tables are supposed to be great, I'm still trying to get to that point. Is there certain situations it just cannot handle?

I have data that is not just a simple Row(Vertical), Header(horizontal) and Data format.

Really what I'm trying to accomplish is something like what would be very simple with SQL, Select employeeJob Description from Table X where employeeName = "Joe Schmoe"

Beyond that I'm doing some formatting but getting the job description carried over and matched to every employee is harder than it should be.

Any Suggestions?
Thanks for all help,

Scott

 

[Ron Coderre]

Pivot Tables don't do much...but what they do, they do very well:
Offer an easy, flexible way to view, summarize, and filter tabular data.

The key is that the data should be formatted as closely as possible to a
database table in 1st Normal Form.
• Each column should contain only one kind of data.
• No two columns should contain the same kind of data (eg CurrAddr1, CurrAddr2)
• Although you *can* have non-unique rows (Those are what pivot tables are great at summarizing.)

If you're starting there, the rest should work.

What's your data structure?

 

[Fazza]

Hi, Scott.

One way is to make the employeeName field a page field and select "Joe Schmoe" in the drop down filter list.

Another way is to start in a separate workbook (to avoid memory leak when creating the pivot table) and at the first step of the wizard take the external data option. If you have used a non-dynamic named range for your data before starting, it will all be straightforward - otherwise OK to the message about no visible tables and then from 'options' hit 'system tables' to see worksheet names. Keep going and go into MS Query when you have the chance and then hit the 'SQL' button and type in the SQL you want. Exit via the 'open door' icon & complete the pivot table. Once created, the worksheet containing the pivot table can if you like be moved into the original file.

HTH, Fazza

 

[Fazza]

PS

The syntax for the SQL is

SELECT `employeeJob Description`
FROM Table X
WHERE employeeName = 'Joe Schmoe'

Or for numerics,

SELECT `employeeJob Description`
FROM Table X
WHERE employeeID_number = 123456

 

[JamieF]

The key is that the data should be formatted as closely as possible to a
database table in 1st Normal Form.
• Each column should contain only one kind of data.
• No two columns should contain the same kind of data (eg CurrAddr1, CurrAddr2)

Ron

If you are starting out with tabular data, e.g. rows for each customer order, and columns that show a delivery schedule (e.g. monthly) for each order, is there any way to take advantage of Pivot tables to summarise this data?

E.g.

OrderA, Customer, Amount, Region, Jan$, Feb$, Mar$, Apr$, etc
OrderB, etc

I've have it formatted like this because the delivery schedule calculates automatically from data earlier in each row.

The sort of data I'd like to get out is graphs of the schedule by region, top customers, etc.

I guess that I'm breaking the rules of database normalisation, and won't get very far!

Thanks

Jamie

 

[Ron Coderre]

E.g.

OrderA, Customer, Amount, Region, Jan$, Feb$, Mar$, Apr$, etc
OrderB, etc
.
.

I guess that I'm breaking the rules of database normalisation, and won't get very far!

Jamie

I'd have to agree with that statement.

 

[Fazza]

Yes, it is not how one would set up the data for a pivot table; though a pivot table could be created from the data, if you really wanted to. Such as, normalise the data via SQL. Best created via a new workbook separate from the data. At the first step of the pivot table wizard take the external data option and via MS Query edit the SQL like below - untested. Again, this is not ideal. HTH

SELECT Order, Customer, Amount, Region, 'Jan' AS [Month], `Jan$` AS [Value]
FROM tbl
UNION ALL
SELECT Order, Customer, Amount, Region, 'Feb' AS [Month], `Feb$` AS [Value]
FROM tbl
UNION ALL
SELECT Order, Customer, Amount, Region, 'Mar' AS [Month], `Mar$` AS [Value]
FROM tbl
etc

In fact, you might also exclude nulls or zero values with just a fraction more complication.

After creation (in the new workbook, to avoid memory leak problems on creation), the worksheet containing the pivot table can be moved back into the source file if you like.

 

[Fazza]

post script
A bit of VBA could create the SQL specific for your fields if you wanted a more flexible solution instead of the manual one described above

 

[JamieF]

Ron - thanks for confirming my thoughts on this.

Fazza - thanks for suggesting a way around this. I'll have to give this a try. Though I am VBA-enabled, I'd prefer to keep the solution as simple as possible, in case others need to maintain it.

The non-normalised bit is calculated from a start date, number of deliveries, and days between deliveries. I could probably skip the "abnormality" and get SQL to generate a separate schedule that is normalised directly from this source data.

 

[Fazza]

Jamie,

Although the approach I outlined will work, it is more to show the task can be done with a pivot table directly from the non-normalised data.

As you're after a simple as possible solution that will be easy for others to maintain, such an approach may not be best. It is a far from common approach in Excel and will unfamiliar to nearly all users.

As you're VBA enabled, I'd suggest a simpler approach would be have some VBA create/update a list of normalised data on another worksheet (maybe simply hidden but accessible to users who need to see it, so not xlveryhidden) and then refresh the pivot table from this data. The list of data could be created many ways - for simplicity, I'd suggest just with repeated simple copy and paste operations. This would be simpler for others to understand and maintain.

HTH, Fazza