I am using Excel 2003 and I am trying to optimize the performance of my workbook. The workbook has several pivot tables based on two separate source files. I am most concerned with 12 of my pivots and want to be able to reduce the number in half (6) to improve the speed of the refresh rate on the workbook. Currently there are 15 pivots in total in the workbook.
A little history: The purpose of the workbook is to show the frequency and number of minutes of downtime on 9 separate manufacturing lines. Of the 15 pivot tables, the first two show downtime data for instances in excess of 20 minutes. The third analyzes downtime for instances less than 20 minutes. From this third table I gather the top 6 downtime instances based on frequency of occurance from formulae surrounding the table. This tells me the top 6 part numbers involved in the DT and the reason for the DT. I then run 6 separate pivot tables, one for each part to give me the detail of the downtime over the last 5 times the part was run. I have to run 6 more pivot tables on these 6 parts to give me the specific DT detail based on the reason for the DT.
I have added 2 attachments to this thread to show you an example of the 2 pivot tables I run for one part. As you can see for this part, When I have the specific DT reason noted (583Pivot - 27938) there are only 2 instances of DT on July 1 and July 6. However, this part's last 5 runs were June 17, 19, 26, July1 and 6 (583Pivotall 27939). I need to graph the specific parts DT but show all 5 runs in the graph - see 3rd attachement 27941 below.
View attachment 583pivot.zip
View attachment 583pivotall.zip
View attachment 583chart.zip
Is there a way using a formula for me to just take only the specific data
for each part for each line (6 x 9 lines = 54 instances) from one pivot table as opposed to using two pivot tables? I thought I could use an Index or Match formula but can't quite get my head around how to get this to work. Or if someone could point me in the direction to learn more about these two formulae and examples of their application.
Sorry if this is so wordy, but I'm not sure on how to explain it any better.
A little history: The purpose of the workbook is to show the frequency and number of minutes of downtime on 9 separate manufacturing lines. Of the 15 pivot tables, the first two show downtime data for instances in excess of 20 minutes. The third analyzes downtime for instances less than 20 minutes. From this third table I gather the top 6 downtime instances based on frequency of occurance from formulae surrounding the table. This tells me the top 6 part numbers involved in the DT and the reason for the DT. I then run 6 separate pivot tables, one for each part to give me the detail of the downtime over the last 5 times the part was run. I have to run 6 more pivot tables on these 6 parts to give me the specific DT detail based on the reason for the DT.
I have added 2 attachments to this thread to show you an example of the 2 pivot tables I run for one part. As you can see for this part, When I have the specific DT reason noted (583Pivot - 27938) there are only 2 instances of DT on July 1 and July 6. However, this part's last 5 runs were June 17, 19, 26, July1 and 6 (583Pivotall 27939). I need to graph the specific parts DT but show all 5 runs in the graph - see 3rd attachement 27941 below.
View attachment 583pivot.zip
View attachment 583pivotall.zip
View attachment 583chart.zip
Is there a way using a formula for me to just take only the specific data
for each part for each line (6 x 9 lines = 54 instances) from one pivot table as opposed to using two pivot tables? I thought I could use an Index or Match formula but can't quite get my head around how to get this to work. Or if someone could point me in the direction to learn more about these two formulae and examples of their application.
Sorry if this is so wordy, but I'm not sure on how to explain it any better.
