Hello, can someone please support me on this topic? I am trying to join two files by specific columns, namely "number" and "item". Additionally, I need another column "notes" created, which will identify common and uncommon numbers between the two files. please see below. Thank You!
File A
| Number | Item | Country | status | Date | Qualification |
| 100 | xyz | BR | yes | 10/25/2023 | 4rb |
| 270 | ijk | DE | yes | 10/26/2023 | 2aa |
| 720 | uvw | FR | no | 10/31/2023 | 6cb |
| 520 | asd | CA | yes | 11/17/2023 | 3br |
File B
| Number | Item | Country | Name | Material | Qualification |
| 100 | xyz | BR | Bob | zinc | 4rb |
| 270 | ijk | DE | Alice | iron | 2aa |
| 250 | aaa | US | Janet | oil | 4ac |
| 101 | qqq | UK | John | wood | r2b |
| 211 | vbm | CN | Smith | sand | 7vm |
| 520 | asd | CA | Thomas | copper | 3br |
Output: will include additional column that checks for numbers not common in both files and other wise.
| Number | Item | Country | status | Date | Qualification | Name | Material | new column"notes" |
| 100 | xyz | BR | yes | 10/25/2023 | 4rb | Bob | zinc | Common number |
| 270 | ijk | DE | yes | 10/26/2023 | 2aa | Alice | iron | Common number |
| 720 | uvw | FR | no | 10/31/2023 | 6cb | - | - | number only in file A |
| 520 | asd | CA | yes | 11/17/2023 | 3br | Thomas | copper | Common number |
| 250 | aaa | US | - | - | 4ac | Janet | oil | number only in file B |
| 101 | qqq | UK | - | - | r2b | John | wood | number only in file B |
| 211 | vbm | CN | - | - | 7vm | Smith | sand | number only in file B |
