Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking for pivot table functionality that shows detail instead of count
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking for pivot table functionality that shows detail instead of count
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. -- Regards Roger Govier "C.B." wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking for pivot table functionality that shows detail instead of count
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking for pivot table functionality that shows detail instead of count
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking for pivot table functionality that shows detail instead of count
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell detail in pivot table | Excel Discussion (Misc queries) | |||
Pivot table count function not counting all data. | Excel Discussion (Misc queries) | |||
Using a Pivot Table Calculated Field to get a Unique Count | Excel Worksheet Functions | |||
pivot table - subtotal below detail | Excel Discussion (Misc queries) | |||
How do I get a pivot table to sum instead of giving me a count? | Excel Worksheet Functions |