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.
- Select Worksheet : Join Worksheets by Column menu;
wjoinbycol -d; in Script Window or Command Window.
This tool utilizes the wjoinbycol X-Function.
Specify the Recalculate Mode.
Specify the input worksheets you want to join. See the details about how to select input worksheets with the display box and toolbar.
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):
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.
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:
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.
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
Specify whether to ignore rows with missing values in the matching columns.
Specify the output worksheet.