1
Consider the following set of data in a table called Employee:
Name Sup Q1Sales Q2Sales Q1Atte Q2Atte John Joe Good Bad Bad Bad Mary Joe Good Bad Bad Bad Bob Sara Bad Good Bad Bad Mary Sara Bad Good Bad Bad Ed Joe Good Good Bad Good Sally Jim Bad Good Good Good
Main data segment is Quarter1 and Quarter2 Sales and Attendence (Atte). For the first record, sales rep John reporting to Joe had Good sales in Q1 and bad sales in Q2. His Q1 attentence was bad and is Q2 attendence was also bad.
If I were to do the following distinct query...
select distinct Q1Sales, Q2Sales, Q1Atte, Q2Atte from Employee
...it would return the following records:
Q1Sales Q2Sales Q1Atte Q2Atte Good Bad Bad Bad Bad Good Bad Bad Good Good Bad Good Bad Good Good Good
Now to my question about an Excel 2010 pivot table. I want to run a full extract of all the data in Employee (without distinct) and paste it into an Excel sheet. I then want to wrap it all in a pivot table. When fully collapsed, I want the pivot table to look like this:
Q1Sales Q2Sales Q1Atte Q2Atte Good Bad Bad Bad Bad Good Bad Bad Good Good Bad Good Bad Good Good Good
However, i want to be able to expand it two levels deep as shown here:
Q1Sales Q2Sales Q1Atte Q2Atte Good Bad Bad Bad Sup Joe Name John Mary Bad Good Bad Bad Sup Sara Name Bob Mary Good Good Bad Good Sup Joe Name Ed Bad Good Good Good Sup Jim Name Sally
Does an Excel pivot table support this type of distinct querying? If I wasn't clear in my question, please let me know what additional information you need.
1I think your biggest challenge is the original data layout. Is changing the data layout a possibility? Either fundamentally altering it or minor mods (e.g. adding helper columns)? Also, is it an actual Excel table or is it data pulled from another source (e.g. SQL)? – dav – 2012-07-12T20:56:09.943
The data layout is actually fine. This a just one-time extract of data. We're converting data in one table over to another and mapping the old values to new values. having a distinctive recordset will allow us to look at all possible combinations of values that exist as well as all possible combinations of values that were converted from the old. The data is pulled from a database, but I was hoping Excel's pivot tables would allow me to have a more dynamic collapsible format. – oscilatingcretin – 2012-07-13T17:48:39.000