Looking for pivot table functionality that shows detail instead of count

[C.B.]

I am trying to maintain a single list with:

AdultName Sequence Date Activity ChildName
---------------------------------------------------------
Teacher1 1 9/1 PickUp C1
Teacher1 1 9/3 Teach C2
Teacher1 1 9/5 DropOff C1
Teacher1 2 9/4 PickUp C3
Teacher1 2 9/9 DropOff C3
Teacher2 1 9/2 PickUp C2
Teacher2 1 9/3 Teach C1
Teacher2 1 9/6 DropOff C2
Teacher2 2 9/7 Teach C3
etc

And generate BOTH of the following:

Schedule 1, Sequential activity list by teacher

Seq 1 Seq 2
PickUp Teach Drop Pickup Teach Drop
------ ----- ---- ------ ----- ----

Teacher1 9/1 9/3 9/5 9/4 9/9
C1 C2 C1 C3 C3

Teacher2 9/2 9/3 9/6 9/7
C2 C1 C2 C3


and Schedule 2, Same data sliced into a calendar

9/1 9/2 9/3 9/4 9/5 9/6 9/7 9/9
----------------------------------------------------

PickUp C1 C2 C3

Teach C1 C3
C2

DropOff C1 C2 C3


I was doing this by putting "=cell" into the two schedules, but data is
constantly shifting so it became a maintenance nightmare.

Then I found Pivot Tables. That allowed me to generate exactly what I
want, except it doesn't show me the details... it only shows me a
function such as "count". Then, when I click on the count, it shows the
actual detail of what was counted.

So, I am hoping I can get some help/pointers here. Is it possible to
have PivotTable display the details of the data instead of a count of
the data? Or, if that is just the wrong tool, any other suggestions
that can help me do this in Excel?

I am a somewhat new user to excel, and new to groups, so I appreciate
any pointers if there is a better way I should be searching for this
answer.

Thanks

 

[Roger Govier]

Hi

For your first table
Drag Teacher, Then Date, then Child to the Row area
Drag Sequence then Activity to the column Area
Drag Count of Date to the Data area.

Double click on any fields as necessary, and set Subtotal to None.

For Table 2
Drag Activity then Child to Row area
Drag Date to Column area
Drag Count of Child to Data area

Again, disable Subtotalling as appropriate.
From the PT wizard, choose Table Options and switch off Grand Total for
Rows and Grand Total for Columns

You won't get exactly as you want, but you will a series of "1's" in the
matrix against each of the appropriate headings.

 

[Herbert Seidenberg]

Since you want the details of the data instead of the count,
or in other words, the date and the child name instead of a set of 1's,
use sum of Date and format the Pivot Table results with a date format.
For the second Pivot Table, replace C1,C2,C3 in the ChildName column
with the numbers 1,2,3 and use sum of ChildName.
Create a custom format (limited to 3 names):
[=1]"John";[=2]"Tim";"Fred"
and format the second Pivot Table results with this custom format.
If you have more than 3 names, you can use a lookup table,
but this adds complexity.

 

[C.B.]

Thanks both for the replys. Using the tricks described here ("sum" on
the date and a vlookup table for names), I am generating the first
table with only a little extra work to maintain a lookup table. I think
I now have nice techniques for loading the details into the data cells
of a pivot table when there is only one value per cell.

The second table still has me stumped. I may be heading down the wrong
path for this one using pivot tables because the table may list
multiple names in any cell. Bad example I made, but look on 9/13..
there are two children getting taught. My current pivot table shows me
the number/count of names (I have as many as 4 in a single cell with my
real data) and I can click on the number to see the details, but with
just the count in the cells I am not able to print and distribute
hardcopies for scheduling.

If anyone has any other tricks I can attempt for my second table, it is
appreciated. I have already greatly impressed my boss with this first
set you folks supplied, so thanks again

 

[Herbert Seidenberg]

Here is a solution for multiple counts in a cell.
Assign a prime number to each ChildName (starting with 3)
and use Product of ChildName.
Then find the prime factors of the resulting Pivot Table product.
For how to generate primes and find factors, search
excel.worksheet.functions
"How to find prime factors of a number"
Nov 29, 2005
Lookup factors to get names and concatenate them.