XL98: Converting Multiple Rows/Columns to Columns/Rows

• Microsoft Excel 98 Macintosh Edition

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

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.

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

Additional query words: XL98 mailing labels convert data formula transpose

Keywords : kbdta xlformula xllist
Version : MACINTOSH:98
Platform : MACINTOSH
Issue type : kbhowto