Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
Converting Multiple Rows/Columns to Columns/Rows
Article ID: 116289 - View products that this article applies to.
This article was previously published under Q116289
In Microsoft Excel, you can use a worksheet formula to covert data that spans multiple rows and columns to a database format (columnar).
The following example converts every four rows of data in a column to four columns of data in a single row (similar to a database field and record layout). This scenario is similar to what you would do when you open a worksheet or text file containing data in a mailing label format.
ExampleTo see an example that converts data, follow these steps:
OFFSET($A$1,(ROW()-f_row)*rows_in_set+INT((COLUMN()- f_col)/col_in_set), MOD(COLUMN()-f_col,col_in_set))
f_row = row number of this offset formula
f_col = column number of this offset formula
rows_in_set = number of rows which make one record of data
col_in_set = number of columns of data
Article ID: 116289 - Last Review: August 15, 2003 - Revision: 1.1