Microsoft KB Archive/116289

= Converting Multiple Rows/Columns to Columns/Rows =

Article ID: 116289

Article Last Modified on 8/15/2003

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q116289





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.</ol>

The formula can be interpreted as follows <pre class="fixed_text">  OFFSET($A$1,(ROW-f_row)*rows_in_set+INT((COLUMN- f_col)/col_in_set),   MOD(COLUMN-f_col,col_in_set)) where: <pre class="fixed_text">  f_row = row number of this offset formula <pre class="fixed_text">  f_col = column number of this offset formula <pre class="fixed_text">  rows_in_set = number of rows which make one record of data <pre class="fixed_text">  col_in_set = number of columns of data

Additional query words: mailing labels convert data formula transpose XL

Keywords: kbhowto KB116289

-

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

© Microsoft Corporation. All rights reserved.