Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'd use a macro here. You have to run it only once.

    Code:
    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

  5. #5
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='788262' date='10-Aug-2009 13:57']I'd use a macro here. You have to run it only once.

    Code:
    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

  6. #6
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.
    Attached Files Attached Files

  8. #8
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •