|
Sorting of a pivot table by outer and inner row field combinations
I use a spreadsheet to track machine issues, minutes, and dates for a group
of machines(D/S). Sample source data: D/S # Date Issue desc. Minutes D/S1 1/1/2007 Issue A 5 D/S1 1/1/2007 Issue B 11 D/S2 1/1/2007 Issue A 6 D/S3 1/1/2007 Issue A 8 D/S1 1/2/2007 Issue A 5 D/S2 1/2/2007 Issue B 2 D/S3 1/2/2007 Issue C 3 D/S3 1/2/2007 Issue D 4 I can run a pivot on the data summing the minutes of each discrete issue with a grouping by date. It is sorted to show top 3 Issues based on sum of minutes. i.e. Sum of Minutes Date Issue desc. D/S # 1/1/2007 - 1/2/2007 Issue A D/S1 10 D/S2 6 D/S3 8 Issue B D/S1 11 D/S2 2 Issue C D/S3 3 What I want is a pivot with the same date grouping that will show me the overall top 3 issues based on the discrete sum of minutes for each D/S. i.e. Sum of Minutes Date Issue desc. D/S # 1/1/2007 - 1/2/2007 Issue B D/S1 11 Issue A D/S1 10 Issue A D/S3 8 Any help on this would be greatly appreciated. |
Sorting of a pivot table by outer and inner row field combinations
Add a field to the source table, and combine the Issue desc and D/S fields:
=C2 & " " &A2 Then, add the new field to the pivot table, replacing the two separate fields. andygoon wrote: I use a spreadsheet to track machine issues, minutes, and dates for a group of machines(D/S). Sample source data: D/S # Date Issue desc. Minutes D/S1 1/1/2007 Issue A 5 D/S1 1/1/2007 Issue B 11 D/S2 1/1/2007 Issue A 6 D/S3 1/1/2007 Issue A 8 D/S1 1/2/2007 Issue A 5 D/S2 1/2/2007 Issue B 2 D/S3 1/2/2007 Issue C 3 D/S3 1/2/2007 Issue D 4 I can run a pivot on the data summing the minutes of each discrete issue with a grouping by date. It is sorted to show top 3 Issues based on sum of minutes. i.e. Sum of Minutes Date Issue desc. D/S # 1/1/2007 - 1/2/2007 Issue A D/S1 10 D/S2 6 D/S3 8 Issue B D/S1 11 D/S2 2 Issue C D/S3 3 What I want is a pivot with the same date grouping that will show me the overall top 3 issues based on the discrete sum of minutes for each D/S. i.e. Sum of Minutes Date Issue desc. D/S # 1/1/2007 - 1/2/2007 Issue B D/S1 11 Issue A D/S1 10 Issue A D/S3 8 Any help on this would be greatly appreciated. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
| All times are GMT +1. The time now is 06:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com