2.2.3.49 wjoinbycol
Brief Information
Restructure: Join Multiple Sheets by Column
Additional Information
Minimum Origin Version Required: 2015 SR0
Command Line Usage
1. wjoinbycol irng:=([Book1]Sheet1,[Book2]Sheet1) condition:="[Book1]Sheet1!A=[Book2]Sheet1!A"
2. wjoinbycol irng:=([Book1]B1,[Book1]B2,[Book2]B1,[Book2]B2) condition:="[Book1]B1!A=[Book1]B2!A=[Book2]B1!A=[Book2]B2!A" unmatch:=1 combine:=0 missing:=1;
X-Function Execution Options
Please refer to this page for additional option switches when accessing the x-function from script.
Variables
Display
Name
|
Variable
Name
|
I/O
and
Type
|
Default
Value
|
Description
|
| Input Worksheets
|
irng
|
Input
Range
|
<unassigned>
|
Specify the input worksheet range.
The syntax is: ([BookName1]SheetName1!, [BookName2]SheetName2!).
|
| Matching Columns
|
condition
|
Input
string
|
<unassigned>
|
Specify the matching column condition to join the worksheets, parallel to the input worksheets defined by irng, use "=" as connector. So all values in the matching column(s) will be compared and the matched rows will be combined as the same row in the result worksheet.
The full syntax is:
[BookName1]SheetName1!ColumnShortName1=[BookName2]SheetName2!ColumnShortName2
When a Long Name is shared among input sheets, 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 input 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.
|
| Sort Output
|
sort
|
Input
int
|
2
|
Control the order of mathed values in the result sheet.Three options are available:
- 0 = asc:Matching Columns -- Ascending
- 1 = desc:Matching Columns -- Descending
- 2 = preserve:Preserve 1st sheet order
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
|
unmatch
|
Input
int
|
0
|
Specify whether to drop the values that do not have a match.
- 0 = do not drop and fill in other columns for non-match values with missing values,
- 1 = drop non-matches
|
| Drop Multiples
|
multiple
|
Input
int
|
0
|
This is supposed to be used when there are multiple matched cells for one value. It is used to specify whether to drop the other data rows other than the first match. 0 = do not drop, 1 = drop.
When multiple is set to 1, only the first match found will be included in the result worksheet.
|
| Match with All Combinations
|
combine
|
Input
int
|
1
|
This is also supposed to be used when there are multiple matched cells for one value. It is used to specify whether to show all possible combinations in result worksheet.
- 0 = do not show all combinations and fill in other columns without combinations with missing values,
- 1 = show all combinations
|
| Merge Matched Columns as One
|
merge
|
Input
int
|
1
|
Specify whether to keep only one matched column in the result worksheet.
- 0 = do not keep, there will be multiple matched columns in result sheet,
- 1 = keep only one matched column in the result worksheet. In this case, only one matching column will be shown in the result worksheet as the first column and the data columns will be arranged side by side.
|
| Consider Missing Value
|
missing
|
Input
int
|
0
|
Specify whether to ignore rows with missing values in the matching columns.
- 0 = if there are missing values in matching column, the whole data row will be ignored in the result sheet,
- 1 = the missing values in the matching column(s) will be treated as a separate group.
|
| Dataset Identifier
|
id
|
Input
int
|
0
|
Specify source dataset indentifier.
0 = None, 1 = Range, 2 = Book Name, 3 = Sheet Name, 4 = Use index constructed in index to identify dadtaset source, 5 = Show sheet label of source worksheet.
|
| Index
|
index
|
Input
string
|
<auto>
|
Available when id = 4. Specify the index of the identifier.
Format is start:increment. For example, >code?1:2</code> will indentify source worksheet as 1,3,5,...
|
| Output Worksheet
|
ow
|
Output
Worksheet
|
<new>
|
Specify the output range, see syntax here.
|
Description
This X-Function can be used via LabTalk script. It can be used to combine multiple worksheets into one, the combination will be determined by matching columns in each input worksheet. Several options are provided to determine how to treat special cases when combining (e.g. multiple matched cells, non-match values, missing values.etc).
If you want to combine two worksheets, you can also consider Join Worksheets by Column tool.
Examples
The following example shows simple cases to combine worksheets with matching column, the data in the matching column is date.
- Create a new Origin project file, use Window:Script Window to open the Script Window, run the following scripts to prepare the source data sheets:
// Create new workbook with 2 worksheets
newbook name:="JoinWksEx" sheet:=2 option:=lsname;
// Fill in column A in Sheet 1 with patterned date from 2014/10/1 to 2014/10/25
patternD irng:=[JoinWksEx]Sheet1!col(A) from:=2456931 to:=2456955 unit:=day;
// Fill in column B in Sheet 1 with row index
patternN irng:=[JoinWksEx]Sheet1!col(B) to:=25;
// Fill in column A in Sheet 2 with patterned date from 2014/10/10 to 2014/10/30 with a different display format
patternD irng:=[JoinWksEx]Sheet2!col(A) display:=19 from:=2456940 to:=2456960 unit:=day;
// Fill in column B in Sheet 2 with patterned index from 10 to 30
patternN irng:=[JoinWksEx]Sheet2!col(B) from:=10 to:=30;
- You should have a worksheet named as JoinWksEx with two worksheets and filled with some data, note that column A in both sheets (with date data) will be used as matching columns, and the display formats are different in the two sheets.
- Now execute the wjoinbycol X-Function and get results for different cases:
// Case 1: combine with default settings, i.e. not to drop non-matches and merge matched column as one
wjoinbycol irng:=[JoinWksEx](Sheet1,Sheet2) condition:="[JoinWksEx]Sheet1!A=[JoinWksEx]Sheet2!A" ow:=[<new>]<new>;
// Case 2: drop non-matches
wjoinbycol irng:=[JoinWksEx](Sheet1,Sheet2) condition:="[JoinWksEx]Sheet1!A=[JoinWksEx]Sheet2!A" unmatch:= 1 ow:=[<new>]<new>;
// Case 3: not to merge matched columns as one
wjoinbycol irng:=[JoinWksEx](Sheet1,Sheet2) condition:="[JoinWksEx]Sheet1!A=[JoinWksEx]Sheet2!A" merge:=0 ow:=[<new>]<new>;
Related X-Functions
wappend, wjoinbylabel, Wjoincols
Keywords:merge, combine
|