Microsoft KB Archive/192357

= XL98: Converting Multiple Rows/Columns to Columns/Rows =

Article ID: 192357

Article Last Modified on 9/11/2002

-

APPLIES TO


 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q192357





SUMMARY
In Microsoft Excel, you can use a worksheet formula to covert data that spans multiple rows and columns to a database format (columnar).



MORE INFORMATION
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.

Example
To see an example that converts data, follow these steps:  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

 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. Select cells C1:F3. On the Edit menu, click Copy. 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

Additional query words: XL98 mailing labels convert data formula transpose

Keywords: kbhowto KB192357

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.