Text Join Function in Excel

Scenario: The assembly items and the corresponding Items have been provided as per the following.

The component column needs to be changed to the different columns based on assembly items. Follow the below

1. Copy the Assembly Items column to the next column(in between the A and B columns. So now columns A and B contain the assembly items and column C has the components.
2. In D2, type the formula – =TEXTJOIN(“|”,TRUE,IF(A2=B:B,C:C,””)) and apply the formula to all the lines.
3. Remove the Column C of components
4. Remove the duplicates in the sheet by ‘Remove Duplicates’ functionality.
5. By using the ‘Text to Columns’ functionality, split the combined cell into different columns