Chapter 25: Importing and Cleaning Data
2525
Changing vertical data to horizontal data
Figure 25.21 shows a common type of data layout that you might see when importing a file.
Each record consists of three consecutive cells in a single column: Name, Department, and
Location. The goal is to convert this data so that each record appears in three columns.
FIGURE 25.21
Vertical data that needs to be converted to three columns
There are several ways to convert this type of data, but here’s a method that’s fairly easy.
It requires a small amount of setup, but the work is done with a single formula, which is
copied to a range.
Start by creating some numeric vertical and horizontal “headers,” as shown in Figure 25.22.
Column C contains numbers that correspond to the first row of each data item (in this case,
the name). In this example, we put the following values in column C: 1, 4, 7, 10, 13, 16, and
- You can use a simple formula to generate this series of numbers.