This post will show the use of a macro in Excel to convert time series data from rows to columns. In the figure below, there is an elevation reading at 8:00 am and 4:00 pm each day. We want to get the elevation data into a single column.
On a new sheet we develop a column with the date and time (column A) and a single column for the elevation values (column B). We begin by putting the first date and time in cell A2. In cell A3, we add 8 hours to the value in cell A2.
In cell A4, we add 16 hours to cell A3.
We can then copy cells A3 and A4 to cells A5:A11. Our date and time column is now ready.
To get the elevation data into a single column, we can record a macro since the steps are repetitive. In the figure below, we name the macro, "data_into_columns", and make a shortcut key of Ctrl+A.
For this example, I copied the data into a different portion of the worksheet. I begin recording the macro with the cursor on row 10. Additionally, for this macro to work correctly, "Use Relative References", must be selected.
The first step is to arrow down one row and insert a new row.
The next step is to arrow up to the second value on row 10. I select CTRL-X, then arrow down to row 11, and select CTRL-V. I then arrow down one row so that the cursor will be in the correct location for the macro. I can then stop recording. These steps are shown in the next three figures below.
With the macro now recorded, I can simply select CTRL-A enough times to move all of the values into a single column.
I can then move this single column next to the date and time that was created earlier.
Comments
Post a Comment