PivotTable Difficulties

[AmyN]

[attachment=85037:Schedule_Pivot.xls]I have a scheduling worksheet that has multiple column headers for different date views (F1:AR5) and some subtotal formulas above the column headers as well. The way I am defining the table array is not transferring to any PivotTable format that is useful.

I am looking to sum schedule time by Activity (C5), by Personnel (D5 & E5), by Category (B5), have % breakdowns of each and have the PivotTable provide breakdowns for Fiscal Quarter (F1:AR1), Year (F2:AR2), Month (F3:AR3), Week (F5:AL:5) and Date (F4:AR4).

How would I define the table arrays to make this happen? Any help if always appreciated.

Amy

[HansV]

Your data table is already in crosstab format so it's not suitable as source data for a pivot table. You must use a database-like table. In the attached version I only converted the first two rows, to give you an idea.

[AmyN]

Hans,
Thanks. That makes sense. Now it puts me in the position of creating (and updating) the crosstab format Sheet1 into a database-like table in Sheet2. I started putting together some formula arrays that starts to do this; however, before I go down the road of trying to fix all of the formula arrays so that when a row value has no value (="") then it performs a one up offset from the records of the starting date. Is such a formula array possible without creating a circular reference? Please advise.

Also, it seems to me that the Sheet2 database-like table can be created with VBA code copying and pasting cell values and records starting with Sheet1!$F$4. I play with some ideas and send them your way. Would that be folly to try? Please advise.

Amy

[HansV]

I'd use a macro here. You have to run it only once.

Sub ConvertTable()
  Dim wsh1 As Worksheet
  Dim wsh2 As Worksheet
  Dim r As Long
  Dim m As Long
  Dim c As Long
  Dim n As Long
  Dim t As Long
  Set wsh1 = Worksheets("Sheet1")
  Set wsh2 = Worksheets("Sheet2")
  ' Clear Sheet2 except for the column headings
  wsh2.Range(wsh2.Cells(2, 1), wsh2.Cells(wsh2.Rows.Count, _
	wsh2.Columns.Count)).ClearContents
  ' Determine last used row and column on Sheet1
  m = wsh1.Cells(wsh1.Rows.Count, 2).End(xlUp).Row
  n = wsh1.Cells(4, wsh1.Columns.Count).End(xlToLeft).Column
  t = 1
  ' Loop through the rows on Sheet1
  For r = 6 To m
	' Loop through the dates
	For c = 6 To n
	  t = t + 1
	  ' Copy data from Sheet1 to Sheet2
	  wsh2.Cells(t, 1) = wsh1.Cells(4, c)
	  wsh2.Cells(t, 2) = wsh1.Cells(r, 2)
	  wsh2.Cells(t, 3) = wsh1.Cells(r, 3)
	  wsh2.Cells(t, 4) = wsh1.Cells(r, 4)
	  wsh2.Cells(t, 5) = wsh1.Cells(r, 5)
	  wsh2.Cells(t, 6) = wsh1.Cells(r, c)
	Next c
  Next r
  ' Format the date column correctly
  wsh2.Columns(1).NumberFormat = "m/d/yyyy"
End Sub

[AmyN]

[quote name='HansV' post='788262' date='10-Aug-2009 13:57']I'd use a macro here. You have to run it only once.

Sub ConvertTable()
  Dim wsh1 As Worksheet
  Dim wsh2 As Worksheet
  Dim r As Long
  Dim m As Long
  Dim c As Long
  Dim n As Long
  Dim t As Long
  Set wsh1 = Worksheets("Sheet1")
  Set wsh2 = Worksheets("Sheet2")
  ' Clear Sheet2 except for the column headings
  wsh2.Range(wsh2.Cells(2, 1), wsh2.Cells(wsh2.Rows.Count, _
	wsh2.Columns.Count)).ClearContents
  ' Determine last used row and column on Sheet1
  m = wsh1.Cells(wsh1.Rows.Count, 2).End(xlUp).Row
  n = wsh1.Cells(4, wsh1.Columns.Count).End(xlToLeft).Column
  t = 1
  ' Loop through the rows on Sheet1
  For r = 6 To m
	' Loop through the dates
	For c = 6 To n
	  t = t + 1
	  ' Copy data from Sheet1 to Sheet2
	  wsh2.Cells(t, 1) = wsh1.Cells(4, c)
	  wsh2.Cells(t, 2) = wsh1.Cells(r, 2)
	  wsh2.Cells(t, 3) = wsh1.Cells(r, 3)
	  wsh2.Cells(t, 4) = wsh1.Cells(r, 4)
	  wsh2.Cells(t, 5) = wsh1.Cells(r, 5)
	  wsh2.Cells(t, 6) = wsh1.Cells(r, c)
	Next c
  Next r
  ' Format the date column correctly
  wsh2.Columns(1).NumberFormat = "m/d/yyyy"
End Sub

[/quote]
Hans,
This worked well. My apologies for the delayed response. I am running up against the row limit for Excel 2003 so I need to tailor the code to convert rows during a specified time period or something. I have 2600 records (rows) and 144 date columns. The math reaches the 65k limit pretty quick, so let me figure out how to formulate my problem.

Thanks
Amy

[AmyN]

I have expanded the primary worksheet with the table data that I cannot create a pivot table from; however, in doing so, I cannot follow all of the number references in the code. I have tried through trial and error, but have only come up with error. Any guidance on what the numbers of the code refer to would be great appreciated.

Does this section of the code:
wsh2.Cells(t, 1) = wsh1.Cells(4, c)
wsh2.Cells(t, 2) = wsh1.Cells(r, 2)
wsh2.Cells(t, 3) = wsh1.Cells(r, 3)
wsh2.Cells(t, 4) = wsh1.Cells(r, 4)
wsh2.Cells(t, 5) = wsh1.Cells(r, 5)
wsh2.Cells(t, 6) = wsh1.Cells(r, c)

,have to reflect as many columns that I am creating in the pasting sheet?

My end game is to create pivot charts that show individual and collective level of effort over time for each category. Data will continually be input and updated in the gray shaded columns of the Activity_Schedule.

Amy

[HansV]

I'm not sure I understand what you want to accomplish, but I have edited the macro in the attached version so that it works with the changed layout. Perhaps you can modify it to suit your needs.

[AmyN]

[quote name='HansV' post='793701' date='17-Sep-2009 11:32']I'm not sure I understand what you want to accomplish, but I have edited the macro in the attached version so that it works with the changed layout. Perhaps you can modify it to suit your needs.[/quote]
Hans,
Thank you this worked well. I'll compare this to the previous solution to discern what the number references are.

Amy