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.

Improve this question
2
  • 1
    I 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
    Commented Jul 12, 2012 at 20:56
  • 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
    Commented Jul 13, 2012 at 17:48

2 Answers 2

Reset to default
1

EDIT: After considering your initial question and looking at your data, I don't think Excel is capable of producing the type of report you seem to want (without a hack or data change as otherwise suggested in both answers to date).

If the report format is the primary focus, and the data format is locked down, then I think you need report writing software like Crystal Reports (or a similar package), that can read down your data and provide a specifically formatted report based upon it. It looks like you're wanting both a summary and detail level report, which Crystal can do pretty easily. If the format is less important and keeping with Excel is critical, you'll need to think outside the box like either two answers (thus far).

With helper columns and conditional formatting, you can create a report like this in Excel:

Pivot Detail

Unfortunately, it's not a pivot table and you can't move your level of detail up or down. With VBA and form controls you might be able to create the level of zoom you described, but I can't think of a native way to accomplish it.

ORIGINAL ANSWER: I don't believe with your current data arrangement you can make the pivot exactly like you want. Here's a quick attempt without any data manipulation:

PivotDistinct

The two pivot tables will provide all the information, but not quite as compact and polished as you suggested.

For both of these, just stack all the fields you're interested in having represented into the Row Lables section of the Pivot Table and choose the Tabular Design in report layout.

Improve this answer
2
  • I'll check out your updated solution in a few days and let you know if it works. Overall, it looks like what I need, but with the higher-level items visible out to the side rather than collapsed. The Name-equivalent column in the real database, though, will have several hundreds of items per Sup-equivalent items, so it might present an aweful lot of scrolling now that I think about it.
    – oscilatingcretin
    Commented Jul 13, 2012 at 17:50
  • I ended up writing my own complicated VBA macro to do what I need. I marked yours as answer becuase you said Excel has no native support for this which technically answered my question.
    – oscilatingcretin
    Commented Sep 28, 2012 at 12:40
0

Any reason why you don't create a unique (distinct) key in the data once extracted? The pivot table will automatically merge the data then.

If I add column 'key' with the following formula:

=CONCATENATE(D2,"-",E2,"-",F2,"-",G2)

Data

I can exactly replicate what you are trying for (I think..) in terms of the levels

Pivot output

Improve this answer
1
  • I hadn't thought of that. Good idea, but it's a more of a hack than a solution. I can't give the business something that looks like that (no key column names; just says "Bad-Good-Bad-Bad"). This is only a sample, of course. The real data has far more columns I need to get distinct levels on.
    – oscilatingcretin
    Commented Jul 12, 2012 at 20:39

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .