4.4.2 Join Worksheets by Column

When you have several worksheets and want to combine them together, considering that there are a reference column which should be matched, you can use the Join Worksheets by Column tool.

To open this tool, with a workbook window activated, you can

  • Select Worksheet : Join Worksheets by Column menu;

Or

  • Run wjoinbycol -d; in Script Window or Command Window.


This tool utilizes the wjoinbycol X-Function.

Join Worksheets by Column 01.png

Dialog Controls

Recalculate

Specify the Recalculate Mode.

  • None
  • Auto
  • Manual

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

Specify the matching column condition to join the worksheets. When Long Name is shared among input sheets, names will list in the Matching Columns drop-down.

If manually specifying columns, the full syntax is: [BookName1]SheetName1!ColumnName1=[BookName2]SheetName2!ColumnName2

You can enter only column Long Name or Short Name (Long Name will be used for matching first) without an equal sign "=". It means all columns with the specified name in all selected sheets should match. For example, if "Time" is entered as Matching Columns, all values in column("Time") of all selected sheets will be compared and the matched rows will be combined as the same row in result worksheet.. If there is no column("Time") in a worksheet, that sheet will be dropped.

The following image illustrates the above method (suppose all options are set to 0):

Join worksheet matching columns.png

Sort Output

Specify how to sort the values in the result sheet according to the matching columns.

  • Matching Columns -- Ascending:Sort the result worksheet by the matching column in ascending order.
  • Matching Columns -- Descending:Sort the result worksheet by the matching column in descending order.
  • Preserve 1st sheet order:Keep the order of matching column in the first input worksheet.

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

Specify whether to drop the values that do not have a match.

When select this box, only the matched values will be included in the result worksheet. For example:

Join worksheet drop nonmatches.png

Options

Drop Multiples

When there are multiple matched cells for one value, you can use this check box to keep the first match found and drop the other repetitive founds.
Join worksheet drop multiples.png

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.
Join worksheet match with all combinations.png

Merge Matched Columns as One

Specify whether to keep only one matched column in the result worksheet.
Join worksheet merge matched columns as one.png

Consider Missing Value

Specify whether to ignore rows with missing values in the matching columns.

Output Worksheet

Specify the output worksheet.