Pivot table simplification

QuestionEdit

QUESTION: Hello -

I have a set of error records from a customer database. For each unique record in the customer database, a series of 10 error checks are performed. The results (Y/N) for each error check performed on the record is stored in a column representing the error check; for example, the results of the first error check are stored in column H, the second in column I, etc. Within each customer record, there is a unique customer ID number and a corresponding contact person ID number. I wish to build a pivot table that displays the total number of errors for each error check for each contact person ID number. How would I construct such a pivot table? A sample test file is available if it will help answer my question.

ANSWER: David:

I have uploaded a sample file here:

home.swbell.net/nate-sus/excel/ForDavid.xls

The key is to use 0 for N and 1 for Y (think binary or TRUE/FALSE instead of a text based Y/N). Once you have done that then you will be able to perform a sum (which is essentially a count in this situation) of the error records.

Good luck!

---------- FOLLOW-UP ----------

QUESTION: Hello Nathan -

Thanks for the guidance. I have transformed my data into zeroes and ones, but I cannot get my pivot table to look as streamlined as your example. My pivot shows a zero and a one in the column section. I am using2003 and am not familiar with how you were able to build the pivot table with the "sums" displayed as columns. Could you explain how I would do that?

Dave

AnswerEdit

David:

When setting up your pivot table you should have "Contact ID" in the Row section (I believe currently you have one of your Error Checks in the "Row section").

In The Values section if the pivot table you should have all 10 of your Error check fields.

If you do not see these settings then right-click on your Pivot Table and select "show field list"

When you are done your "Column section" will have the"Values" in it...which is fine. The Report filter should be blank.