| October 2003 Tip of the Month | | Tip of the Month Archive | |
Converting Blocks of Data to a Single Column
Frequently data sources present information in "block form." This is illustrated in worksheet 'sheet1' where monthly data are available in rows of twelve columns with each row representing a year of data. It is usually convenient to have the data reorganized as a single sequential column of data.
The data reorganization can be easily accomplished on a second worksheet using the OFFSET function. First fill column A with monthly dates. Then enter the function
=OFFSET(Base_cell,Year(A1)-Year($A$1), Month(A1)-1,1,1)
in cell B1, where Base_cell points to the upper left-hand data position
of the block of data. Finally, drag the function in cell B1 down column
B to fill in the column of data.
The same reorganization can be accomplished for quarterly data arranged in individual year rows as illustrated in sheet3.
In this case construct a date column on a new worksheet using the final month of each quarter (March, June, September and December). Then enter the function
=OFFSET(Base_cell,Year(A1)-Year($A$1), Month(A1)/3-1,1,1)
in cell B1, where Base_cell points to the upper left-hand data position
of the block of data. Finally, drag the function in cell B1 down column
B to fill in the column of data.
