Results 1 to 2 of 2
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Breaking Up A Master List (2000)

    Worksheet 1, Column A consists of dates by day beginning with 1/1/1965 and proceeding through 12/31/2004. Column B on the same worksheet consists of daily prices for each of those days. What I would like to do on another worksheet is "break up" the dates and respective data by years, so that column A2:A367 would be a "Master List" of dates (including 2/29), columns B, C, D, E, etc., being the data for the respective years that would be in cell 1 of each column (i. e., B1 would be "1965", with B2 being the price for January 1 of 1965, C1 would be 1966, with C2 being the price for January 1 of 1966). But for leap years every 4 years, it would be a simple matter of breaking up the master list on worksheet 1 into equal 365 cell pieces. Any ideas as to maybe a "Match", etc., shortcut?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Breaking Up A Master List (2000)

    Enter Year, Month and Day in C1, D1 and E1, respectively.
    Enter the following formulas in C2, D2 and E2:
    =Year(A2)
    =Month(A2)
    =Day(A2)
    Select C22 and double click the fill handle (the little black square in the lower right corner of the selection). This should fill down the formulas as far as needed.
    Select A1.
    Select Data | PivotTable and PivotChart Report...
    Click Next twice, then specify the layout of the pivot table:
    - Drag Month to the row area
    - Drag Day to the row area (below Month)
    - Drag Year to the column area
    - Drag the price field to the data area.
    As a bonus, you get monthly and yearly subtotals, but you can remove those if you want by double clicking the gray Month or Year button and setting Subtotals to None.

Posting Permissions

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