This Page: cpearson.com/Excel/ColumnToTable.aspx

Last Updated: 06-May-2018

Copyright 1997 - 2014, Charles H. Pearson

Site Last Updated: 21-Jun-2020

Advanced Software

Design And Development

Design And Development

Office Integration Projects
NET Programming
XML Development

Search The Site:

Column To Table

This page describes formulas you can use to transform a column of data into
a table of rows and columns.

Introduction

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.

You can download the file with all the example formulas on this page. |

Using VBA To Transform A Column

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() '''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' ColumnToTable ' This transforms the column of data with a defined name ' of "ColumnData" to a two dimensional table whose upper ' left cell is named "StartTable". The block size of ' the data in ColumnData is specified by C_BLOCK_SIZE. '''''''''''''''''''''''''''''''''''''''''''''''''''''''' Dim ColumnData As Range ' The original column of data Dim RNdx As Long ' Row index Dim CNdx As Long ' Column index Dim StartRow As Long ' Row number of Range("StartTable") Dim StartColumn As Long ' Column number of Range("StartTable") Dim N As Long ' Index into ColumnData Dim WS As Worksheet ' Worksheet reference Const C_BLOCK_SIZE = 5 ' Block size of data in ColumnData ''''''''''''''''''''''''''' ' Initialize the variables. ''''''''''''''''''''''''''' Set ColumnData = Range("ColumnData") StartRow = Range("StartTable").Row StartColumn = Range("StartTable").Column RNdx = StartRow CNdx = StartColumn Set WS = Worksheets("UsingVBA") N = 0 '''''''''''''''''''''''''''''''''''''' ' Loop across then down filling ' cells with element N of ColumnData. '''''''''''''''''''''''''''''''''''''' 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

You can download the file with all the example formulas and VBA code on this page. |

This page last updated: 11-November-2008. |