Combining Two Files by Specific Columns and Identifying Common Fields.

[OIT]

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

[kamal03]

Hi @OIT 

You can achieve this by using a simple join tool and adding a formula tool to it.

Please find the below workflow, please do let me know if it worked for you or not.

[OIT]

Hi @kamal03 ,

Thank you so much! this worked perfectly.