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

JDM-LTCS

New Member
Joined
Sep 7, 2006
Messages
40
Thanks in advance! :biggrin:

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. :unsure:


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)
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
After you've created the Pivot Table, there's a drop down that lets you select/filter what you want displayed.
 

JDM-LTCS

New Member
Joined
Sep 7, 2006
Messages
40
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. :cry:

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?
 

JDM-LTCS

New Member
Joined
Sep 7, 2006
Messages
40
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. :)
 

Forum statistics

Threads
1,136,619
Messages
5,676,847
Members
419,655
Latest member
pd2021vb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top