Select the product you need help with

Converting Multiple Rows/Columns to Columns/Rows

Article ID: 116289 - View products that this article applies to.

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).

Back to the top | Give Feedback

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

Back to the top | Give Feedback

Properties

Article ID: 116289 - Last Review: August 15, 2003 - Revision: 1.1

APPLIES TO

Microsoft Excel 97 Standard Edition

kbhowto KB116289

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top | Give Feedback