MERGE

You are here:

AXCEL.MERGE function

Merge tables with a database-style join such as inner-join, outer-join, left-join and right-join with single or multiple keys.

Syntax

AXCEL.MERGE(LeftData, RightData, [LeftKeys], [RightKeys], [JoinType])


The AXCEL.MERGE function syntax has the following arguments:

LeftData Required. This is the left dataset for merging tables. It should be presented as a table with the first row of column names.

RightData Required. This is the right dataset for merging tables. It should be presented as a table with the first row of column names.

LeftKey Optional. If there are common column names between two tables, Axcel automatically choose those columns as the keys for merging. Otherwise, you need to explicitly identify the name of columns used for mering in the left table.

RightKey Optional. If there are common column names between two tables, Axcel automatically chooses those columns as the keys for merging. Otherwise, you need to explicitly identify the name of columns used for mering in the right table.

JoinType Optional. Default is “i” for inner join which requires each row in the two joined tables to have matching column values. The types of joins available in this function are:

"i" or "I" for Inner Join (default): which requires each row in the two joined tables to have matching column values.
"o" or "O" for Outer Join: The joined table retains each row—even if no other matching row exists.
"l" or "L" for Left Join: Similar to outer join but retains only rows in the left table.
"r" or "R" for Right Join: Similar to outer join but retains only rows in the right table. 

For example, we have two tables (located at A1 to C5:

namesalarydepartment
jack100A
jill200A
sophia150B
smith300B

and this one located at E1 to G7:

first_namegroupranking
jackA1
davidA2
roseC2
sophiaD2
joeE3
jillF3

Since “name” in the first table and “first_name” in the second table are the common keys, we define them in the function. For Inner Join, we run:

=AXCEL.MERGE(A1:C5,E1:G7,”name”,”first_name”,”I”)

which produces this table:

namesalarydepartmentfirst_namegroupranking
jack100AjackA1
jill200AjillF3
sophia150BsophiaD2

For Outer Join we run:

=AXCEL.MERGE(A1:C5,E1:G7,”name”,”first_name”,”O”)

which produces this table:

namesalarydepartmentfirst_namegroupranking
jack100AjackA1
jill200AjillF3
sophia150BsophiaD2
smith300B   
   davidA2
   roseC2
   joeE3

For Left Join, we run:

=AXCEL.MERGE(A1:C5,E1:G7,”name”,”first_name”,”L”)

namesalarydepartmentfirst_namegroupranking
jack100AjackA1
jill200AjillF3
sophia150BsophiaD2
smith300B   

Lastly, for Right Join, we run:

=AXCEL.MERGE(A1:C5,E1:G7,”name”,”first_name”,”R”)

which produces this table:

namesalarydepartmentfirst_namegroupranking
jack100AjackA1
   davidA2
   roseC2
sophia150BsophiaD2
   joeE3
jill200AjillF3

You can also define multiple keys for each table For instance, in our previous example, if you would like to run an Inner Join with name and department columns in the left table and first_name and group in the right table, you can run:

=AXCEL.MERGE(A1:C5,E1:G7,{“name”,”department”},{“first_name”,”group”},”I”)

which gives you this table:

namesalarydepartmentfirst_namegroupranking
jack100AjackA1

See other data management function.