4.4.1 Appending Worksheets to Single Sheet


You can combine multiple worksheets to be one new worksheet with the Append Worksheet tool. These worksheets can be appended column by column, or row by row.

To access this tool
  1. Activate the intended worksheet.
  2. Click Resturcture: Append Worksheet... and open the wAppend dialog box.

It utilizes the wAppend X-Function.

Notes: The worksheet style formats and embedded graph will be lost in the output worksheet after you append worksheets together.

Another option for those who need to join data from several sources -- particularly, files of the same file type and structure -- would be to use a Data Connector to import multiple files, appending each file to the first file by this method.

Dialog Control

Recalculate

Specify the Recalculate Mode.

  • None
  • Auto
  • Manual
Worksheets

This part is used to specify the input worksheets to be combined. It includes a display box and a toolbar with four buttons Group List Toolbar append worksheet.png:

  • Display Box
    The selected worksheets will be displayed in this box. The sequence of the worksheet names displaying in this box determines the sequence they are used for appending.
  • Triangle Button for Select Button Group List Add.png
    Specify the input worksheet(s) by choosing an option from the context menu:
    Sheets in Book
    Including all flat worksheets in the current active workbook.
    Sheets in Folder
    Including all flat worksheets in the current active Project Explorer folder.
    Sheets in Folder (Open)
    Including all flat worksheets in the current active Project Explorer folder that are open (not minimized).
    Sheets in Folder (Recursive)
    Including all flat worksheets in the current active Project Explorer folder and all its sub folder.
    Sheets in Project
    Including all flat worksheets in the current Origin project
    Include Hidden Sheet
    Including the hidden worksheets in the current Origin project
    Select Worksheets
    Open the Sheet Browser to select worksheets.
    Reset
    Clear current selection
  • Remove button Button Group List Remove.png
    Click it to remove the selected worksheet(s) from the Display Box. This button will not be activated if there is no worksheet in the Display Box (indicated by <None>).
  • Move Up button Button Group List Move Up.png
    Move up the selected worksheet(s) in the Display Box so the appending sequence changes.
  • Move Down button Button Group List Move Down.png
    Move down the selected worksheet(s) in the Display Box so the appending sequence changes.
Range

Specify whether to append entire worksheet or specified columns.

  • Entire Worksheet
    Entire worksheet will be appended.
  • Specified Columns
    Specified columns in worksheet will be appended.
Columns

This option is only available when Specified Columns is selected as Range. This part is used to specify the input columns to be combined. It includes a display box and a toolbar with four buttons Group List Toolbar append worksheet.png:

  • Display Box
    The selected columns will be displayed in this box. The sequence of the column names displaying in this box determines the sequence they are used for appending.
  • Triangle Button for Select Button Group List Add.png
    Common Columns
    Select one or multiple common column(s) that exist(s) in all selected worksheets.
    Custom Columns
    Open the Column Browser to select columns.
    Reset
    Clear current selection
  • Remove button Button Group List Remove.png
    Click it to remove the selected column(s) from the Display Box. This button will not be activated if there is no column in the Display Box (indicated by <None>) or no column is selected.
  • Move Up button Button Group List Move Up.png
    Move up the selected column(s) in the Display Box so the appending sequence changes.
  • Move Down button Button Group List Move Down.png
    Move down the selected column(s) in the Display Box so the appending sequence changes.
Append by

Specify the direction to append worksheets or columns.

  • Column
    The worksheets/columns will be appended as new columns.
  • Row
    The worksheets/columns will be appended as new rows.
Append to End of Each Column

When you set Append by = Row, this check box will be available.

When you checked it, the rows to append will be appended to the end of each column without considering max boundary of its worksheet.

When you unchecked it, and if the columns in worksheets have different sizes on row, the worksheet will be appended under the last row of the longest column, with missing values filling the blank cells.

Data Identifier Specifies what data source information will be added into the output worksheet, to show where the source sheets/columns came from.
  • None: Do not show source dataset identifier.
  • Range: Show the Range of source worksheet.
  • Book Name: Show the Name of source workbook.
  • Sheet Name: Show the Name of source worksheet.
  • Index: Show the Index of source worksheet, which is specified by Index.
  • Sheet Label: Show the sheet label of source worksheet.

Please note, When Append by = Column, the data identifier will be added into the label row "Source" ;when Append by = Row, the data identifier will be added into the last column with "Source" as long name. If Append to End of Each Column is checked, this control will be disable.

Insert Identifier Before Data Specify insert the Dataset Identifier you chose as one column column before the data columns. Otherwise the identifier column will append to the end column.

By default, it is unchecked. If Append to End of Each Column is checked, this control will be disable.

Index Specify the Index of source worksheet to show in output. Available when Dataset Identifier is Index.

The syntax is: start:increment, e.g. 0:0.5(0, 0.5, 1, 1.5...), default is <auto>, which equals to 1:1.

Output Worksheet Specifies where to output the combined worksheet.