Summary
When you use the Microsoft Excel products listed at the bottom of this article, 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 is a similar scenario as that which you experience when you open a worksheet or text file that contains data in a mailing label format.
Example
-
In a new worksheet, type 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
-
Type the following formula in cell C1:=OFFSET($A$1,(ROW()-1)*4+INT((COLUMN()-3)),MOD(COLUMN()-3,1))
-
Fill this formula across to column F, and then down to row 3.
-
Adjust the column sizes as necessary. Note that the data is now displayed in cells C1 through F3 as follows:
Smith, John
111 Pine St.
San Diego, CA
(555) 128-549
Jones, Sue
222 Oak Ln.
New York, NY
(555) 238-1845
Anderson, Tom
333 Cherry Ave.
Chicago, IL
(555) 581-4914
The formula can be interpreted as
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 that make one record of data
-
col_in_set = number of columns of data
