2.2.3.48 wjoinbycol


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;

Variables

Display
Name
Variable
Name
I/O
and
Type
Default
Value
Description
Input Worksheets irng

Input

Range

<unassigned>

This is used to specify the input worksheet range.

The syntax is: ([BookName1]SheetName1!, [BookName2]SheetName2!).

Matching Columns condition

Input

string

<unassigned>

This is used to specify the matching column condition for each input worksheet.

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

So all values in the matching column(s) will be compared and the matched rows will be combined as the same row in result worksheet. The idea is illustrated in the following image (suppose all option variables in the table below are set to 0):

Join worksheet matching columns.png

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)

When unmatch is set to 1, only the matched values will be included in the result worksheet. For example:

Join worksheet drop nonmatches.png

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. For example:

Join worksheet drop multiples.png

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)

For example, see the case of ID = a when combine is set to 1 as follows:

Join worksheet match with all combinations.png

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.

Join worksheet merge matched columns as one.png

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). For details, please refer to the Variables table above.

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