Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Array or Pivot Tables???
Hi, I am trying to find a more efficient means of getting my period data. Below is an example of code that I use to get the current period's performance data for a given activity by taking the difference from the current period's cumulative data and the previous period's cumulative data. Each contract block has 4 to 8 hulls. Each contract block's cumulative data for a given period has approximately +50K rows of data. Pivot tables are quick but I have to break the hulls up so that I can fit the current and previous data on one sheet. If I use an array or index/match functions it takes about +20 minutes to generate the period data. Is there a better way to use an array that is capable of approaching the speeds at which a pivot table is able to perform this task? wbC -- worksheet where the current cum data is wbP -- worksheet where the previous cum data is wb3 -- worksheet where the matched period data is placed '################################################# ##### 'LOAD CURRENT ARRAY cntCur = wbC.Cells(Rows.Count, "A").End(xlUp).Row arrCur = wbC.Range("A1:M" & cntCur) 'LOAD PREVIOUS ARRAY cntPrev = wbP.Cells(Rows.Count, "A").End(xlUp).Row arrPrev = wbP.Range("A1:M" & cntPrev) For C = 1 To cntCur Call ShowUserForm 'Progress Bar wb3.Cells(C, 1) = arrCur(C, 1) 'Corp wb3.Cells(C, 2) = arrCur(C, 2) 'Hull wb3.Cells(C, 3) = arrCur(C, 3) 'MM wb3.Cells(C, 4) = arrCur(C, 4) 'KE wb3.Cells(C, 5) = arrCur(C, 5) 'LT wb3.Cells(C, 6) = arrCur(C, 6) 'HT wb3.Cells(C, 7) = arrCur(C, 7) 'ACTIVITY wb3.Cells(C, 8) = arrCur(C, 8) 'DESC wb3.Cells(C, 9) = arrCur(C, 9) 'CURRENT TAC wb3.Cells(C, 10) = arrCur(C, 10) 'CURRENT TCWS wb3.Cells(C, 11) = arrCur(C, 11) 'CURRENT TCWP wb3.Cells(C, 12) = arrCur(C, 12) 'CURRENT ACWP For P = 1 To cntPrev 'Match Current Acitvity with Previous Acitvity If arrCur(C, 1) & arrCur(C, 2) & arrCur(C, 3) & arrCur(C, 4) = _ arrPrev(P, 1) & arrPrev(P, 2) & arrPrev(P, 3) & arrPrev(P, 4) Then If C = 1 Then '1st pass skip & insert headers for period data GoTo MoveOn1 Else wb3.Cells(C, 13) = arrCur(C, 9) - arrPrev(P, 9) 'per TAC wb3.Cells(C, 14) = arrCur(C, 10) - arrPrev(P, 10) 'per TCWS wb3.Cells(C, 15) = arrCur(C, 11) - arrPrev(P, 11) 'per TCWP wb3.Cells(C, 16) = arrCur(C, 12) - arrPrev(P, 12) 'per ACWP wb3.Cells(C, 17) = _ (arrCur(C, 11) - arrPrev(P, 11)) - (arrCur(C, 12) - arrPrev(P, 12)) 'per CV wb3.Cells(C, 18) = _ (arrCur(C, 11) - arrPrev(P, 11)) - (arrCur(C, 10) - arrPrev(P, 10)) 'per SV End If 'Match found step out of Previous Loop GoTo MoveOn1 End If Next P MoveOn1: If C = 1 Then 'Insert period headers on 1st pass wb3.Cells(C, 13) = "perTAC" wb3.Cells(C, 14) = "perTCWS" wb3.Cells(C, 15) = "perTCWP" wb3.Cells(C, 16) = "perACWP" wb3.Cells(C, 17) = "perCV" wb3.Cells(C, 18) = "perSV" ElseIf P cntPrev Then 'No activity matched wb3.Activate wb3.Range(Cells(C, 13), Cells(C, 18)).Select For Each cell In Selection.Cells cell.Value = 0 Next End If Application.StatusBar = _ "Row " & Format(C, "#,###") & " of " & Format(cntCur, "#,###") Next C Thanks in advance, Lonnie M. P.S. Harlan and Alan, play nice now ;) |
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Correction, the previous loop's 'If' statement should be:
For P = 1 To cntPrev 'Match Current Acitvity with Previous Acitvity If arrCur(C, 4) & arrCur(C, 5) & arrCur(C, 6) & arrCur(C, 7) = _ arrPrev(P, 4) & arrPrev(P, 5) & arrPrev(P, 6) & arrPrev(P, 7) Then |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Pivot tables - Editing the data source shared by multiple pivot ta | Excel Discussion (Misc queries) | |||
Building pivot tables in Excel 2007 based on existing pivot tables? | Excel Discussion (Misc queries) | |||
Average Calculations from Pivot Tables - Get Pivot Data? Calc Fiel | Excel Worksheet Functions | |||
How does the term 'pivot' apply to Excel's Pivot tables and Pivot. | Excel Discussion (Misc queries) | |||
Pivot tables-controlling user interaction with pivot tables | Excel Programming |