2.2.3.48 wjoinbycol


Menu Information

Worksheet: Join Worksheets by Column

Brief Information

Join multiple worksheets by matching columns

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 for each input worksheet.

The full syntax is: [BookName1]SheetName1!ColumnShortName1=[BookName2]SheetName2!ColumnShortName2

Sort Output sort

Input

int

2

Control the order of mathed values in the result sheet.Three options are available:

  • asc:Matching Columns -- Ascending {0}
  • desc:Matching Columns -- Descending {1}
  • preserve:Preserve 1st sheet order {2}

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)

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)

When merge is set to 1, 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)

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).

You can got the Origin Help page about this tool to learn the details of the functionality of this tool.

Examples

The following example shows simple cases to combine worksheets with matching column, the data in the matching column is date.

  1. 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:
  2. // 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;
  3. 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.
  4. Now execute the wjoinbycol X-Function and get results for different cases:
  5. // 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


Keywords:merge, combine