You are currently offline, waiting for your internet to reconnect

# Converting Multiple Rows/Columns to Columns/Rows

##### SUMMARY
In Microsoft Excel, you can use a worksheet formula to covert data thatspans multiple rows and columns to a database format (columnar).
The following example converts every four rows of data in a columnto four columns of data in a single row (similar to a database fieldand record layout). This scenario is similar to what you would do when youopen a worksheet or text file containing data in a mailing label format.

### Example

To see an example that converts data, follow these steps:
1. In a new worksheet enter the following data:
A1: Smith, John
A2: 111 Pine St.
A3: San Diego, CA
A4 (555) 128-549
A5: Jones, Sue
A6: 222 Oak Ln.
A7: New York, NY
A8: (555) 238-1845
A9: Anderson, Tom
A10: 333 Cherry Ave.
A11: Chicago, IL
A12: (555) 581-4914
2. Enter the following formula:
`      C1: =OFFSET(\$A\$1,(ROW()-1)*4+INT((COLUMN()-3)),MOD(COLUMN()-3,1))						`
Fill this formula across to column F and down to row three.
3. Select cells C1:F3.
4. On the Edit menu, click Copy.
5. With the same range of cells selected, click Paste Special on the Edit menu and then click Values. Click OK.
The formula can be interpreted as follows
`   OFFSET(\$A\$1,(ROW()-f_row)*rows_in_set+INT((COLUMN()- f_col)/col_in_set),   MOD(COLUMN()-f_col,col_in_set))				`
where:
`   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				`
mailing labels convert data formula transpose XL
Properties

Article ID: 116289 - Last Review: 08/15/2003 15:23:29 - Revision: 1.1

• Microsoft Excel 97 Standard Edition
• kbhowto KB116289