Chapter 25: Importing and Cleaning Data
2525
FIGURE 25.23
A single formula transforms the vertical data into rows.
You can easily adapt this technique to work with vertical data that contains a differ-
ent number of rows. For example, if each record contained ten rows of data, the column C
header values would be 1, 11, 21, 31, and so on. The horizontal headers would consist of val-
ues 1 through 10 rather than 1 through 3.
Notice that the formula uses an absolute reference to cell A1. That reference won’t change
when the formula is copied, so all of the formulas use cell A1 as the base. If the data begins
in a different cell, change $A$1 to the address of the first cell.
The formula also uses “mixed” referencing in the second argument of the OFFSET function.
The C2 reference has a dollar sign in front of C, so column C is the absolute part of the ref-
erence. In the D1 reference, the dollar sign is before the 1, so row 1 is the absolute part of
the reference.
See Chapter 9, “Introducing Formulas and Functions,” for more about using mixed references in
formulas.