Results 1 to 2 of 2
Thread: Breaking Up A Master List (2000)
2005-01-11, 10:07 #1
- Join Date
- Jan 2001
- 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?
2005-01-11, 10:32 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 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:
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 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.