Thank you for the opportunity to help you with your question!
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:
Sorting data allows us to re-organize the data in ascending or descending order with respect to a specific variable. Some procedures in SPSS require that your data be sorted in a certain way before the procedure will execute. There are two options for sorting data:
- Sort Cases (i.e., row sort)
- Sort Variables (i.e., column sort)
We cover how to perform each sorting option below.
Sorting cases will rearrange the rows based on a given variable (or variables). The values for the selected variables can be sorted in ascending (smallest to largest, or alphabetical) or descending order (largest to smallest, or reverse alphabetical).
Sorting from the Data View
In the Data View, you can quickly sort your data with respect to a single variable by right-clicking on the variable name and selecting Sort Ascending or Sort Descending.
Sorting with the Sort Cases procedure
If you want to sort your data with respect to two or more variables, or if you want to have the sorted data written to a new file, you'll want to use the Sort Cases procedure:
Click Data > Sort Cases.
Double-click on the variable(s) you want to sort your data by to move them to the Sort by box. If you are sorting by two or more variables, then the order that the variables appear in the "Sort by" list matters. You can click and drag the variables to reorder them within the Sort by box.
In the Sort Order area, you can choose an “Ascending” or “Descending” sort order for each variable in the "Sort by" list. Click on the variable in the Sort by box to highlight it, then click the radio button that corresponds to your sort order choice.
In this example, the data will first be sorted by class rank in ascending order, and will then by sorted by birthdates in descending order. That is, the birthdates will be sorted within each category of class rank. Notice that an “(A)” appears to the right of variables that will be sorted in ascending order, and a “(D)” appears to the right of variables that will be sorted in descending order.
(Optional) If you wish to save your newly sorted data to a new file, select the Save file with sorted data check box in the Save Sorted Data area. Then click File to specify a name and location for the new dataset file.
- When you are finished, click OK.
You can check that your cases were sorted correctly by visually inspecting the data in Data View. In this example, cases have been sorted according to class rank, and then sorted by most to least recent birthdate.
Note: SPSS considers missing values the "smallest" value, so they will appear first if sorting in ascending order, and will appear last if sorting in descending order.
This syntax performs the same sort shown in the screencap above (sort by ascending class rank, then descending birth date).
SORT CASES BY Rank(A) bday(D).
Sorting variables will rearrange the order of the variables (columns) in your data. Variables can be sorted on only one attribute: Name, Type, Width, Decimals, Label, Values, Missing, Columns, Align, Measure, or a custom attribute. Variables can be sorted in ascending or descending order with respect to the selected attribute.
To sort variables, follow these steps:
Click Data > Sort Variables.
The Variable View Columns list includes the attributes of variables that may be used to sort variables, including: name, type, width, decimals, label, values, missing, columns, align, measure. Select an attribute by clicking it in the list, which will highlight your selection. Note that you can only select one variable attribute. In this example, “Name” has been selected, which means that variables will be sorted according to their names.
In the Sort Order area, variables can be sorted in “Ascending” or “Descending” order. Click the radio button that corresponds to your choice. In this example, variables will be sorted in ascending order.
You may also choose to save the current (pre-sorted) variable order in a new attribute by selecting the Attribute name check box and typing a name into the text field. Custom attributes are simply user-defined characteristics of variables that can be used to sort variables.
When you are finished, click OK.
Now your variables will be sorted according to the attribute you selected. In this example, the variables are sorted in ascending order according to their names (i.e., alphabetically).
Content will be erased after question is completed.