[Tutorial] Rearrange rectangular data values

Post by **MrProgrammer** » 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

  A      B      C
1 One    Two    Three
2 Four   Five   Six

into Sheet2

Code: Select all

  A
1 One
2 Two
3 Three
4 Four
5 Five
6 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

  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

  A
1 Six
2 Five
3 Four
4 Three
5 Two
6 One

Or you could read data backwards by columns.

Code: Select all

  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

  A      B      C      D
1 Data   LabelA LabelB LabelC
2 LabelD One    Two    Three
3 LabelE Four   Five   Six
4 LabelF Seven  Eight  Nine

into Sheet2, the simple table.

Code: Select all

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

Reshape.ods: (17.69 KiB) Downloaded 2439 times

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