‹‹ Back to SVS Home

Working with Multiple Spreadsheets

[next] [prev] [prev-tail] [tail] [up]

5.4 Working with Multiple Spreadsheets

Difference Between Appending or Joining Two Spreadsheets

There are two ways to combine spreadsheets: you can append or join two spreadsheets. An append is used when you want to add more rows and both spreadsheets have more or less the same columns. A join is used when you want to add more columns and both spreadsheets have more or less the same rows.

Appending Spreadsheets

To append, select File > Append Spreadsheets and select a spreadsheet to append to the current spreadsheet. There are three options for appending as detailed below.

  • New Dataset Name: A new dataset name can be specified, otherwise the default name is Appended Spreadsheet. One convention is to name the appended spreadsheet “A appended to B” where A is an identifiable name (possibly shortened) for the current spreadsheet, and B is an identifiable name for the spreadsheet to be appended.
  • Handle Unmatched Columns: Non-matching columns between the two spreadsheets can be dropped or filled with missing values. Indicate your choice by clicking on the appropriate radio button.
  • Spreadsheet as Child of: You can also set the spreadsheet to be a child of the project root or of the current spreadsheet. If a child of the project root is selected there are no dependencies between the appended spreadsheet and the two original spreadsheets, and these sheets can be deleted without deleting the appended dataset.
Joining or Merging Spreadsheets

To join or merge, select File > Join or Merge Spreadsheets and select a spreadsheet to join to the current spreadsheet.

The current spreadsheet is referred to as the left spreadsheet and the spreadsheet being joined is referred to a right spreadsheet. This convention is used because the new columns are added to the right of the columns of the current spreadsheet. If a spreadsheet is marker mapped the name will appear in green text, otherwise the name of the spreadsheet will be in blue text.

There are five options for joining spreadsheets, as detailed below.

  • New Dataset Name: A new dataset name can be specified, otherwise the default name is “A + B” where A is the name of the left spreadsheet, and B is the name of a right spreadsheet to be joined.
  • Matching Criteria: Two spreadsheets can be joined based on row labels, if the row labels for the two spreadsheets are more or less identical. To join based on row labels click on the radio button in front of “Use row labels”. If two spreadsheets are to be joined but do not have identical row labels, the rows can be matched based on a custom sort order. The standard custom sort order is used if the left and right spreadsheets are sorted in the correct order for joining, and the desired row labels are the labels of the left spreadsheet.

    If a different sort order of one or both of the spreadsheets, or a different row label convention is desired, click on the radio button for the custom sort order and then click on Define. This dialog allows you to sort both the left and right spreadsheets by any of the first 20 columns. You can sort either ascending or descending by clicking on the column headers. Individual rows can be moved by clicking on a row and clicking on Move Up or Move Down. There are three options for the row labels of the joined spreadsheet: use the labels of the left spreadsheet, use the labels of the right spreadsheet, or join the labels of both spreadsheets by an underscore. The edited custom sort order will be displayed on the Spreadsheet Join dialog.

  • Unmatched Rows: If there are any rows that do not match either by row labels or by custom sort order, you can choose to drop the rows that do not match, or to keep and consider the empty cells to be missing data.

    NOTE:

    • Using custom sort order when the two spreadsheets do not have the same number of rows will result in unmatched rows.
  • Columns with the Same Name: There are three ways to deal with columns sharing the same name. They can be kept as separate columns or the column from either the left or right sheet can be kept with missing values filled in from the other sheet (if the data is not missing there as well).
  • Spreadsheet as Child of: The joined spreadsheet can be created as a child of the project root or the current spreadsheet. In either case the joined spreadsheet will be dependent on both the left and right spreadsheets, so neither of these spreadsheets can be deleted without also deleting the joined spreadsheet.

    NOTE:

[next] [prev] [prev-tail] [front] [up]