Pivot Table - large data rows of text need to be counted

[JDM-LTCS]

Thanks in advance!

What I have is a data collection tool in which I collect data about each classroom I visit based on the fields below. Each classroom creates a new row of data. Each field(column) has either 3 or 4 different options (abcd or efg). What I'm wanting to do is create a pivot table in which I can select which school's data to display, which date of data to display and then see the counts and/or percentages of each field.

e.g. For a particular school on a particular day, I'd like to see how many a's, b's, c's and d's were in Furniture Arrangement as well in Instructional Activities, Instructional methods and how many e's, f's, and g's were in the remaining fields.


Does this make sense? I hope so because I could sure use the help. I guess I'm just not analytical enough for pivot tables.


School = 19 diff names
Date = multiple dates
Furniture Arrangement = 4 diff options (a, b, c, or d)
Instructional Activities = 4 diff options (a, b, c, or d)
Instructional Methods = 4 diff options (a, b, c, or d)
Projector = 3 diff options (e, f, or g)
SmartBoard = 3 diff options (e, f, or g)
Computer = 3 diff options (e, f, or g)
Comp Cart = 3 diff options (e, f, or g)
Doc Camera = 3 diff options (e, f, or g)
Dig Camera = 3 diff options (e, f, or g)
Camcorder = 3 diff options (e, f, or g)
Scanner = 3 diff options (e, f, or g)
Handhelds = 3 diff options (e, f, or g)
Other = 3 diff options (e, f, or g)

 

[Brian from Maui]

After you've created the Pivot Table, there's a drop down that lets you select/filter what you want displayed.

 

[JDM-LTCS]

Correct, but therein lies my problem. I'm not sure of where to put each item in the pivot table layout. I've been rearranging the fields for about...the whole day now trying to create a table that readable and shows the information I'm looking for.

For example, do I put date and school-name in the "page" part of the layout or do I place one of them in the "row" part of the layout?

And when I place all of the fields I wish it to count up, it gives me every possible combination of those fields. a, aa, ab, ac, ad, b, ba, bb, bc, bd, etc.

What I'd love to see is a count of how many a's, and b's and c's and d's in the first field, then a count of how many a's, and b's and c's and d's in the second field, then a count of how many a's, and b's and c's and d's in the third field, then a count of how many a's, and b's and c's and d's in the fourth field, etc.


Does that make sense?

 

[Brian from Maui]

Here's a couple of links,

http://peltiertech.com/Excel/Pivots/pivottables.htm

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=553

 

[JDM-LTCS]

Thanks, I'll take a look at those when my eyes aren't so blurry. The wife and I just got back from spending time with her sister. I got to hold my new nephew for a few hours tonight. Pretty cool considering my wife's about 5 months pregnant.