4.4.3 Join Two Sheets by Column


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

Note: If you have more than two worksheets to join together, please use the Join Multiple Sheets by Column tool.


To open this tool,

  • with a workbook window active, select Restructure: Join Two Sheets by Column menu.
Join Worksheets by Column 01.png

This tool utilizes the Wjoincols X-Function.

Recalculate

Specify the Recalculate Mode.

Specify worksheets and columns to join

The left panel of the dialog has two column tables, w1 and w2. Checkboxes under each table control the settings of its owned sheet, respectively.

  1. Select two sheets to join from Sheet1 (w1) and Sheet2 (w2) drop-down list, or ... button next to it.
  2. From the column tables, select the columns to combine in the joined worksheet. Unchecked columns will be excluded from the result sheet.

Control Options

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.

Join worksheet drop nonmatches.png

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.

Join worksheet drop multiples.png

Specify matching columns

Matching Columns

Check the matching columns in w1 and w2 column table and click -> button to add the condition of joining worksheets in Matching Columns table. If multiple conditions exist, all conditions must be matched.

For example, if w1.A=w2.A is specified in Matching Columns, all values in column A of both sheet w1 and w2 will be compared and the matched rows will be combined as the same row in the joined worksheet.. Suppose all options are set to 0:

Join worksheet matching columns.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 matching column in the joined worksheet.

Join worksheet merge matched columns as one.png

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.

Output worksheet

Sort Output

Control how to sort the order of matched values in the joined sheet:

  • Matching Columns -- Ascending
  • Matching Columns -- Descending
    If Merge Matched Columns as One: merge all matched values into one column. If not selected, Matching Columns – Ascending/Descending will sort output columns of each source sheet respectively.
  • Preserve sheet order: sort by order of matched values found in the sheet specified in Sheet.
  • by Specified Columns -- Ascending: sort the joined worksheet by column specified in Column ascendingly.
  • by Specified Columns -- Descending: sort the joined worksheet by column specified in Column descendingly.

Dataset Identifier

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

  • If None, identifier label row will not be added.
  • When Merge Matched Column by On is checked, and Range/Book Name/Sheet Name/Index is selected as Identifier, Source label row of the matched column will show "Merged".
  • Index: identify source sheet by index specified in Index. Format is StartIndex:Increment. For example, 1:2 will identify columns from w1 as "1" and w2 as "3".
  • Sheet Label: Show the sheet label of source worksheet.
English | Deutsch | 日本語