Page 1 of 1

### [Tutorial] Rearrange rectangular data values

Posted: Wed Sep 26, 2012 10:47 pm
At times, you may want to rearrange rectangular data values in Calc. For example, you may want to change Sheet1
Code: Select all   Expand viewCollapse view
`  A      B      C1 One    Two    Three2 Four   Five   Six`
into Sheet2
Code: Select all   Expand viewCollapse view
`  A1 One2 Two3 Three4 Four5 Five6 Six`
or vice versa.

Skip the paragraph in grey if you just want to use my attachment to rearrange your data and don't care how it works.

This can be accomplished by entering formulas in Sheet2 to pull the data from Sheet1, then replacing those formula results with their values. Sheet2.A5 can use the ROW() function to determine that this cell is the fifth entry in the list. Dividing 5 by 3, the number of columns in the data on Sheet1, we get 1 remainder 2. The INT() and MOD() functions give that quotient and remainder. The quotient 1 tells us that the corresponding cell on Sheet1 is after the first three items in row 1 and so the item is somewhere in row 2. The remainder tells us that it is the second item in row 2. Sheet2.A5's formula can use the OFFSET() function to select the item "Five" in row 2 column 2 of Sheet1. The formulas get more complicated when the items in Sheet2 form a multi-column array, and there are some calculation details that I've omitted for simplicity, but this is the general idea. The attached spreadsheet, Reshape.ods, shows the messy details if you're interested.

Reshape.ods, rearranges data values as specified by several parameters on the first sheet. Normally you will rearrange only the data and not any headings which might accompany the data. Items are read from a rectangular array in sequence and written to another rectangular array in sequence. The dimensions of the two arrays normally are different. In the example above the first array has dimension two rows and three columns, while the second array has dimension six rows and one column. Items are read and written in rows going left to right, then top to bottom by default, producing the column of data shown above. However you can read items in columns going top to bottom, then left to right, resulting in:
Code: Select all   Expand viewCollapse view
`  A1 One2 Four3 Two4 Five5 Three6 Six`

Or you can optionally read data backwards, by rows, from the end of the array to the beginning.
Code: Select all   Expand viewCollapse view
`  A1 Six2 Five3 Four4 Three5 Two6 One`

Or you could read data backwards by columns.
Code: Select all   Expand viewCollapse view
`  A1 Six2 Three3 Five4 Two5 Four6 One`

If it seems more logical to you, it's possible to read the data in the default order but write it to the target array by columns or backwards. It is important to note that the process rearranges data values, not formulas, and not formats. However, if you've formatted your data using Styles, it should be relatively simple to reapply the styles to the rearranged data.

Using multiple steps, you can also use the process to convert a contingency table to a simple table. Say we want to convert Sheet1, the contingency table,
Code: Select all   Expand viewCollapse view
`  A      B      C      D1 Data   LabelA LabelB LabelC2 LabelD One    Two    Three3 LabelE Four   Five   Six4 LabelF Seven  Eight  Nine`
into Sheet2, the simple table.
Code: Select all   Expand viewCollapse view
`   A        B        C 1 RowLabel ColLabel Data 2 LabelD   LabelA   One 3 LabelD   LabelB   Two 4 LabelD   LabelC   Three 5 LabelE   LabelA   Four 6 LabelE   LabelB   Five 7 LabelE   LabelC   Six 8 LabelF   LabelA   Seven 9 LabelF   LabelB   Eight10 LabelF   LabelC   Nine`

• Use Reshape.ods to convert Sheet1.B2:D4 (3×3), the data, into Sheet2.C2:C10 (9×1).
• Use Reshape.ods to convert Sheet1.B1:D1 (1×3), the column labels, into Sheet2.B2:B10 (9×1). Note that when the target array (nine items) is larger than the source array (three items), the process "wraps around" and reuses the source data as many times as needed to fill the target array. In cases where the target is smaller than the source, part of the source data will go unused.
• Use Reshape.ods to convert Sheet1.A2:A4 (3×1), the row labels, into Sheet3.A1:C3 (3×3).
Code: Select all   Expand viewCollapse view
`  A      B      C1 LabelD LabelE LabelF2 LabelD LabelE LabelF3 LabelD LabelE LabelF`
• Use Reshape.ods to convert Sheet3.A1:C3 (3×3), reading in columns, into Sheet2.A2:A10 (9×1).
• Enter your new headings, "RowLabel", "ColLabel", and "Data" into Sheet2 cells A1, B1, and C1.
Reshape.ods