Biggish pivot... ? (in 2003...)

[KFozzy]

Hi All


I have an annual audit to prepare two reports for. (Lexcel - if anyone from the law field from UK)

There is a document audit for about 20-50 employees each month which includes 23 questions to which the answer is either "Yes", "No", "n/a" or "See details in notes".

We must collect the info on paper and then the answers are entered into and Excel sheet.

So now I have a worksheet with columns for month, employee and the 23 questions.


I'm trying to generate a pivot table to summarise all this info, but not having much luck. Tried to put the 23 question into rows, and then I tried it with columns, but they don't play nice... when I try to populate the data field with the count of the same 23 questions all hell breaks lose... It just starts to break down into an incredible maze of sub totals and counts - even if I chose not to include sub totals.

I might be overlooking some simple action here, but I suspect this should be fairly easy.

Sample:

Month - Name - Q1......Q23

Nov - KZF - Yes..... n/a

Nov - AAB - No...... Yes

Dec - KZF - n/a..... See details

Dec - LTF - Yes..... Yes

etc. etc.

Again, each month have anything between 20 and 50 records (sample employees picked randomly).

Am I trying too hard? What would be a simple way of getting to know throughout the day how many Yes, No, etc. answers each employee had?

How can I show how many yes, no, etc. answers did we all generate any given month?

Am I making any sense?


thank you all.

 

[Hui]

KFozzy


Firstly, Welcome to the Chandoo.org forums.


Your data needs to be arranged as

Month, Name, Question No, Answer

Nov, KZF, 1, Yes

Nov, KZF, 2, No

Nov, KZF, 3, Yes

.

.

Nov, KZF, 23, No

Nov, AAB, 1, No

etc


Then import into a Pivot table

 

[KFozzy]

Thank you Hui - that's what I call thinking outside of the box...

I will try convert my data into this format straight away!