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 |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
