Column To Table
This page describes formulas you can use to transform a column of data into
a table of rows and columns.
Users are often faced with the task of converting a single column of data into a two-dimensional table
of rows and columns. This transformation can be done with a simple formula. For example, suppose you have in
column A a list of Name, Address, City, State, and Zip Codes for many employees. This list has 5 elements per employee,
as shown in the image on the left below, and you want to create a two dimensional table as shown in the image on
the right below:
This transformation can be done with a single formula entered into the cells in which you want the two dimensional
table to appear. This page will present two formulas, one simple formula for use if your column of data has a fixed
number of elements, and a second formula, an enhancement of the first, for use if your column of data is variable in
size.
As an example, assume that your column of original data begins in cell B4. (In the image above,
cell B3 contains the (optional) header Column Data and the actual data to be
transformed, shown in colored fonts, begins in cell B4. The data in both the original
columns and transformed tables are shown in colored fonts for illustration purposes. In practice, the color is entirely
irrelevant.) Next, assume that the first cell of the transformed table (not including the Tablular Data header)
begins in cell F4.
The first formula can be used if the number of rows in the column of data is fixed. The formula is shown below:
=OFFSET($B$4,(BlockSize*(ROW()-ROW(F$4)))+(COLUMN()-COLUMN($F4)),0,1,1)
Since the data in the original column of data is grouped into sections of 5 rows, we use a defined name called
BlockSize with a value of 5 in the formula. You can use a defined name as in this example
formula, or you can hard code the number within the formula itself, or you can reference another cell. All that matters
is that the block size somehow gets into the formula. Enter the formula above into cell F4
and fill across row 4 for 5 columns (or the number of columns equal to the block size of your columnar data). With this
formula now in cells E4:J4, fill that range down so it fills the range
F4:J9. This range has 6 rows, but the column of data has only 3 blocks of data. Thus, the
remaining cells in F4:J9 are filled with 0s, as shown below.
This is most likely undesirable, which leads us to the second formula presented on this page.
If you original data column has a variable number of rows, but does have a maximum number of rows that will not be
exceeded, you can use an enhancement of the formula above to fill the two dimensional table with blanks if the table
contains more cells that there are rows in the column of data. This formula is shown below:
=IF((BlockSize*(ROW()-ROW(F$14)))+(COLUMN()-COLUMN($F14))>=ROWS($B$4:$B$18),"",
OFFSET($B$4,(BlockSize*(ROW()-ROW(F$14)))+(COLUMN()-COLUMN($F14)),0,1,1))
This formula is split in to two lines for clarity, but should be all on a single line on your worksheet.
Enter this formula in cell F14, fill across to cell J14, and
then fill this range down to fill the range F14:J19. The actual number of rows in this
table should be equal to the maximum number of blocks of data in the original columnar data. For example, if the block
size is 5 and your column of data will have no more than 100 rows (= 20 blocks), the table should have 20 rows and
5 columns. If there is not enough data in the original column of data to fill the entire table, unused entries in the
table are filled with empty strings, as shown below:
The reverse of these operations, transforming a table into a single row or a single column, is described on the
Table To Column page.
In addition to the formula approach described above, you can use VBA code to automate the transformation of a data
column to a two dimensional table. The code below will transform a column of data that has a defined name of
ColumnData to a two dimensional table, the upper left cell of which has a defined
name of StartTable. The block size of the data in the column is specified by the
C_BLOCK_SIZE constant.
Sub ColumnToTable()
Dim ColumnData As Range
Dim RNdx As Long
Dim CNdx As Long
Dim StartRow As Long
Dim StartColumn As Long
Dim N As Long
Dim WS As Worksheet
Const C_BLOCK_SIZE = 5
Set ColumnData = Range("ColumnData")
StartRow = Range("StartTable").Row
StartColumn = Range("StartTable").Column
RNdx = StartRow
CNdx = StartColumn
Set WS = Worksheets("UsingVBA")
N = 0
For RNdx = StartRow To (StartRow + (ColumnData.Rows.Count / C_BLOCK_SIZE))
For CNdx = StartColumn To StartColumn + C_BLOCK_SIZE - 1
N = N + 1
WS.Cells(RNdx, CNdx).Value = ColumnData.Cells(N, 1)
Next CNdx
Next RNdx
End Sub
|
This page last updated: 11-November-2008. |