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 |
