XL: Converting Multiple Rows/Columns to Columns/Rows

• MIcrosoft Excel 98 Macintosh Edition
• Microsoft Excel for Windows, versions 3.x, 4.x, 5.0, 5.0c
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel 97 for Windows
• Microsoft Excel for the Macintosh, versions 3.x, 4.x, 5.0, 5.0a

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 three rows of data in a column to three 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:

1. In a new worksheet enter the following data:

         A1: Smith, John
         A2: 111 Pine St.
         A3: San Diego, CA
         A4: Jones, Sue
         A5: 222 Oak Ln.
         A6: New York, NY
         A7: Anderson, Tom
         A8: 333 Cherry Ave.
         A9: Chicago, IL
   

2. Enter the following formula:

         C1: =OFFSET($A$1,(ROW()-1)*3+INT((COLUMN()-3)),MOD(COLUMN()-3,1))
   

   Fill this formula across to column E and down to row three.

3. Select cells C1:E3.

4. Choose Copy from the Edit menu.

5. With the same range of cells selected choose Paste Special from the Edit menu and select Values. Choose the OK button.

   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