4.4.2 Join Multiple Sheets by Column

join-multi-sheets-bycol

When you have multiple worksheets and want to combine them by column, considering that there are reference columns which should be matched, you can use the Join Multiple Sheets by Column tool.

To open this tool,

• with a workbook window active, select Restructure: Join Multiple Sheets by Column menu.

This tool utilizes the Wjoinbycol X-Function.

Recalculate

Specify the Recalculate Mode.

Input Worksheets

Specify the input worksheets you want to join. See the details about how to select input worksheets with the display box and toolbar.

Matching Columns

• When a Long Name is shared among input sheets, you can click on one sheet in Input Worksheets, and then select the Long Name of the Matching Column in the dropdown list.

• If the Matching Column with the same Short Name among input sheets, you can enter the column Short Name in this edit box.

• If the matching columns without the same Long Name or Short Name among input sheets, you can use "=" as connector for matching column for each sheet, and enter Book Name, Sheet Name, Column Short Name in this edit box.
  The full syntax is:
  

[BookName1]SheetName1!ColumnShortName1=[BookName2]SheetName2!ColumnShortName2=[BookName3]SheetName3!ColumnShortName3

Sort Output

Control the order of matched values in the result sheet.

• Matching Columns -- Ascending

• Matching Columns -- Descending

• Preserve 1st sheet order (By default)

If Merge Matched Columns as One is not selected, Matching Columns – Ascending/Descending will sort output columns rather than matching columns by the first sheet order.

Drop Non-matches

To exclude the unmatched cells from the joined worksheet, check this checkbox. Otherwise, columns of non-match values will fill with missing values.

Options

Drop Multiples

This is supposed to be used when there are multiple matched cells for one value. Check this checkbox to merge the multiples by statistics value specified in Merge Multiples by and the replica will be dropped.

Merge Multiples by: First, Last, Max, Min, Average & Sum.

When the Drop Non-matches option is checked, and set Merge Multiples by to Average, you get the result like this:

Match with All Combinations

When there are multiple matched cells for one value, you can check this check box to show all possible combinations in result worksheet.

Merge Matched Columns as One

Specify whether to keep only one matched column in the result worksheet.

Consider Missing Value

Check this checkbox to consider missing value in the matching columns as a value to be compared. Uncheck will ignore rows with missing value.

Dataset Identifier

Specify whether to add a parameter label row named Source to the joined worksheet for identifying the source of dataset.

• None: identifier label row will not be added.
• Range /Book Name /Sheet Name: show Range/Book Name/Sheet Name as Identifier
• Index: identify source sheet by index specified in Index. Format is StartIndex:Increment.
  For example, 1:1 will identify source worksheets as 1,2,3,...
• Sheet Label: Show the sheet label of source worksheet.

Output Worksheet

Specify the output worksheet.