Can you help me construct a slightly complex Pivot Table in Excel?

May 14, 2009 9:23 AM

Can you help me construct a slightly complex Pivot Table in Excel?

I have columns that look like this:

Order Number | Region | Customer Type | Customer

A customer may have multiple orders, so the data would look like this:

0001 | Region 1 | Type 1 | Customer 1
0002 | Region 1 | Type 2 | Customer 2
0002 | Region 1 | Type 2 | Customer 3
0003 | Region 1 | Type 1 | Customer 1

I want a pivot table that shows me this:

Region 1
Count of Customer Type 1 | Count of Unique Customers within Type 1
Count of Customer Type 2 | Count of Unique Customers within Type 2

Region 2
Count of Customer Type 1 | Count of Unique Customers within Type 1
Count of Customer Type 2 | Count of Unique Customers within Type 2

So I can see, OK Type 1 and Type 2 look equal if I just look at order counts, but by adding count of unique customers, I can see that type 1 is actually being inflated by a major client.

posted by willnot to computers & internet (6 comments total)

I can't think of a way to count the number of unique values within a Pivot table alone.

I put Region, Customer Type and Customer all in the ROW area and put Order Number in the DATA area and made it "Count of Order Number". This enabled me to see not only that one customer had a bigger number of orders, but which customer that was. It worked well for a small data set, but it might not scale up very well.
posted by soelo at 9:46 AM on May 14

I'm assuming a smaller number of customers since you're using excel. A quick way to do this would be create a pivot table starting on column B. Rows for "Type", Columns for "Customer", Value as Count of "Order Number". This creates a column for each customer.. The in column A put the formula "=count(b9:xfd9)" to count the number of Customers that had orders. Switching between Rows and Columns if needed.
posted by FuManchu at 10:19 AM on May 14

There are thousands of customers in even a limited data set, so putting them in columns wouldn't work very well. I'm using Excel because that's the swiss army knife analytic tool that I best understand. I've definitely run up against row limits for some of the reports, so we may ultimately need to move to Crystal or something, but it will need to be Excel in the short-term anyway.

soelo's suggestion was the first thing I tried, and it can give me a quick sense of it, but it doesn't scale well, and it's more detail then our end users would really like to have reported to them.
posted by willnot at 10:40 AM on May 14

I've definitely run up against row limits for some of the reports, so we may ultimately need to move to Crystal or something, but it will need to be Excel in the short-term anyway.

I'm guessing that since you're saying this, that you're not using Excel 2007, which has for practical purposes no row limits.

But, in Excel 2007, I can right-click the subtotal or summary rows of the pivot table and select 'Value Field Settings' or 'Field Settings'. Then I can choose the function that the total or subtotal calculates - currently it'll be SUM, but you could change it to COUNT.

I'm not sure I've understood your problem properly, but I think this is what you're after.
posted by I_pity_the_fool at 11:07 AM on May 14

Eh, beaten by soelo.
posted by I_pity_the_fool at 11:08 AM on May 14

This'll take a few steps then. My solution would be:

First create a key with Region &"|"& Type &"|"& Customer, and make a pivot table to weed out the duplicates. Next to the pivot table break the Region and Type back out. Your "Unique Customer Count in Type X within Region Y" becomes a countif() using the Region and Type of those de-duplicated rows. The "Count of Customers' would still come from the original data.

Would this work?
posted by FuManchu at 11:59 AM on May 14