# Calculate the Intersection, Union, and Difference of Row-Style Data

## Example

### Example 1: Calculate intersection, union and difference for key columns

There is an Excel file Book1.xlsx, and the data is as follows:

 team member1 member2 member3 member4 member5 9 Taylor Stephanie Stephanie David Amanda 7 Ian Angela Brooke Steven Timothy 5 Nicholas Michael Madeline Antony Logan 3 Sophia Jacob Andrew Alexis Brianna 6 Christopher Alyssa Benjamin Thomas Courtney 1 Hannah Isabella Abigail Logan Mary 2 Benjamin Stephanie Jennifer Jacob Jose 10 Katherine Jonathan Kayla Isabella Luis 8 Daniel Justin Dylan Amanda Hailey 4 Kayla Hailey Jennifer Austin Matthew

There is an Excel file Book2.xlsx, and the data is as follows:

 team member1 member2 member3 member4 member5 10 Daniel Kayla Sarah Luis Ryan 15 Abigail Hailey Antony Madison Timothy 11 Haley Stephanie Justin Joseph Bryan 13 Timothy Joseph Hailey Katherine Lauren 12 Ryan David Isabella Julia Jose 9 Julia Haley Elizabeth Julia Alyssa 14 Jose Christian Claire Dylan Jose 8 Christian Lauren Justin Austin Chloe

Calculate the intersection, union, and difference between two sets of data according to the team column.

Write SPL script:

 A 1 =file("Book1.xlsx").xlsimport@t() 2 =file("Book2.xlsx").xlsimport@t() 3 =[A1,A2].merge@ou(team) 4 =[A1,A2].merge@oi(team) 5 =[A1,A2].merge@od(team)

A1 Read the Excel data

A2 Read the Excel data

A3 Union them by team column, and the result is as follows:

 team member1 member2 member3 member4 member5 9 Taylor Stephanie Stephanie David Amanda 7 Ian Angela Brooke Steven Timothy 5 Nicholas Michael Madeline Antony Logan 3 Sophia Jacob Andrew Alexis Brianna 6 Christopher Alyssa Benjamin Thomas Courtney 1 Hannah Isabella Abigail Logan Mary 2 Benjamin Stephanie Jennifer Jacob Jose 10 Katherine Jonathan Kayla Isabella Luis 8 Daniel Justin Dylan Amanda Hailey 4 Kayla Hailey Jennifer Austin Matthew 15 Abigail Hailey Antony Madison Timothy 11 Haley Stephanie Justin Joseph Bryan 13 Timothy Joseph Hailey Katherine Lauren 12 Ryan David Isabella Julia Jose 14 Jose Christian Claire Dylan Jose

A3 Intersect them by team column, and the result is as follows:

 team member1 member2 member3 member4 member5 9 Taylor Stephanie Stephanie David Amanda 10 Katherine Jonathan Kayla Isabella Luis 8 Daniel Justin Dylan Amanda Hailey

A5 Calculate the difference by team column, and the result is as follows:

 team member1 member2 member3 member4 member5 7 Ian Angela Brooke Steven Timothy 5 Nicholas Michael Madeline Antony Logan 3 Sophia Jacob Andrew Alexis Brianna 6 Christopher Alyssa Benjamin Thomas Courtney 1 Hannah Isabella Abigail Logan Mary 2 Benjamin Stephanie Jennifer Jacob Jose 4 Kayla Hailey Jennifer Austin Matthew

### Example 2: Calculate the intersection, union and difference of the entire row

There is an Excel file Book1.xlsx, and the data is as follows:

 team member1 member2 member3 member4 member5 9 Taylor Stephanie Stephanie David Amanda 7 Ian Angela Brooke Steven Timothy 5 Nicholas Michael Madeline Antony Logan 3 Sophia Jacob Andrew Alexis Brianna 6 Christopher Alyssa Benjamin Thomas Courtney 1 Hannah Isabella Abigail Logan Mary 2 Benjamin Stephanie Jennifer Jacob Jose 10 Katherine Jonathan Kayla Isabella Luis 8 Daniel Justin Dylan Amanda Hailey 4 Kayla Hailey Jennifer Austin Matthew

There is an Excel file Book2.xlsx, and the data is as follows:

 team member1 member2 member3 member4 member5 10 Daniel Kayla Sarah Luis Ryan 15 Abigail Hailey Antony Madison Timothy 11 Haley Stephanie Justin Joseph Bryan 13 Timothy Joseph Hailey Katherine Lauren 12 Ryan David Isabella Julia Jose 9 Julia Haley Elizabeth Julia Alyssa 14 Jose Christian Claire Dylan Jose 8 Christian Lauren Justin Austin Chloe

Calculate the intersection, union, and difference between two sets of data based on the entire row.

Write SPL script:

 A 1 =file("Book1.xlsx").xlsimport@t() 2 =file("Book2.xlsx").xlsimport@t() 3 =[A1,A2].merge@ou() 4 =[A1,A2].merge@oi() 5 =[A1,A2].merge@od()

A1 Read the Excel data

A2 Read the Excel data

A3 Union them by the entire row, and the result is as follows:

 team member1 member2 member3 member4 member5 9 Taylor Stephanie Stephanie David Amanda 7 Ian Angela Brooke Steven Timothy 5 Nicholas Michael Madeline Antony Logan 3 Sophia Jacob Andrew Alexis Brianna 6 Christopher Alyssa Benjamin Thomas Courtney 1 Hannah Isabella Abigail Logan Mary 2 Benjamin Stephanie Jennifer Jacob Jose 10 Katherine Jonathan Kayla Isabella Luis 8 Daniel Justin Dylan Amanda Hailey 4 Kayla Hailey Jennifer Austin Matthew 10 Daniel Kayla Sarah Luis Ryan 15 Abigail Hailey Antony Madison Timothy 11 Haley Stephanie Justin Joseph Bryan 13 Timothy Joseph Hailey Katherine Lauren 12 Ryan David Isabella Julia Jose 9 Julia Haley Elizabeth Julia Alyssa 14 Jose Christian Claire Dylan Jose 8 Christian Lauren Justin Austin Chloe

A4 Intersect them by the entire row, and the target result does not exist because there is no same record in the two sets of data.

A5 Calculate the difference set by the entire row, and the result is as follows:

 team member1 member2 member3 member4 member5 9 Taylor Stephanie Stephanie David Amanda 7 Ian Angela Brooke Steven Timothy 5 Nicholas Michael Madeline Antony Logan 3 Sophia Jacob Andrew Alexis Brianna 6 Christopher Alyssa Benjamin Thomas Courtney 1 Hannah Isabella Abigail Logan Mary 2 Benjamin Stephanie Jennifer Jacob Jose 10 Katherine Jonathan Kayla Isabella Luis 8 Daniel Justin Dylan Amanda Hailey 4 Kayla Hailey Jennifer Austin Matthew