Identify Unique Name and Merge/Separate Unique Codes and Join

[ssripat3]

Hello Community,

I have a set of data similar to below. Now, my data has more columns than this. But the action I would like to perform is based on these three columns mainly.

Name	Code	Extracted Code
FPO-R3TD-1000-AA	J9B,N06,1X2,7PB	AVEBP,DR--B,EN-C0,HTHAH,AVEBP,DR--D,EN-C0,HTHAH
FPO-R3TD-1000-AA	L0L,T65,1X1,7PB	AVEBP,DR--F,EN-C2
FPO-R3TD-1000-AA	L0R,T65,1X1,7PB	AVEBP,DR--H,EN-C2
FPO-NCRT-9000-AA	L0L	DR--B,DR--F
FPO-NCRT-9000-AA	L0R	DR--D,DR--H

Now, my objective is to have only unique values in Name Field and the reason for these multiple values is due to the code field.

I would like to try to attain either of the two below desired outputs.

Output 1

In this scenario, I would like to identify the unique "Name" value and append all the data in the "Extracted Code" column using a ";" separator.

Name	Code	Extracted Code
FPO-R3TD-1000-AA	J9B,N06,1X2,7PB	AVEBP,DR--B,EN-C0,HTHAH,AVEBP,DR--D,EN-C0,HTHAH;AVEBP,DR--F,EN-C2;AVEBP,DR--H,EN-C2
FPO-NCRT-9000-AA	L0L	DR--B,DR--F;DR--D,DR--H

Output 2

In this scenario, I would like to consider the unique names as a set and then fetch all the values in "Code" column across multiple rows and identify duplicates.

For example. the Name field FPO-R3TD-1000-AA has codes as below

J9B,N06,1X2,7PB

L0L,T65,1X1,7PB

L0R,T65,1X1,7PB

Now, I would like to remove identical values and then create a new column with values similar to the below table.

Name	Code	Fixed Code
FPO-R3TD-1000-AA	J9B,N06,1X2,7PB	J9B,N06,1X2,L0L,T65,1X1,L0R
FPO-NCRT-9000-AA	L0L	L0L,L0R

[messi007 · Accepted solution]

@ssripat3,

Please find attached how you can do that 

Best,

[ssripat3]

Thank you for the solution @messi007