### [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

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:

Or you can optionally read data backwards, by rows, from the end of the array to the beginning.

Or you could read data backwards by columns.

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,

Note that questions are not allowed in the Tutorials section of the forum. Ask them in the Applications > Calc section.

- Code: Select all Expand viewCollapse view
`A B C`

1 One Two Three

2 Four Five Six

- Code: Select all Expand viewCollapse view
`A`

1 One

2 Two

3 Three

4 Four

5 Five

6 Six

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
`A`

1 One

2 Four

3 Two

4 Five

5 Three

6 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
`A`

1 Six

2 Five

3 Four

4 Three

5 Two

6 One

Or you could read data backwards by columns.

- Code: Select all Expand viewCollapse view
`A`

1 Six

2 Three

3 Five

4 Two

5 Four

6 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 D`

1 Data LabelA LabelB LabelC

2 LabelD One Two Three

3 LabelE Four Five Six

4 LabelF Seven Eight Nine

- 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 Eight

10 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 C`

1 LabelD LabelE LabelF

2 LabelD LabelE LabelF

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

Note that questions are not allowed in the Tutorials section of the forum. Ask them in the Applications > Calc section.