How to split values/text from two column format with reference to a column into multiple columns?

Here is the data:

Desired Output

I tried using Group By function in the Power query but was unable to create additional columns to transform the data. Please guide on this.

>Solution :

This task can be accomplished quite easily using POWER QUERY as well, which will be the easiest approach. Available in Windows Excel 2010+ and Excel 365 Windows or Mac

• First convert the source range into a table and name it accordingly, for this example I have named it as Table1

• Next, open a blank query from Data Tab –> Get & Transform Data –> Get Data –> From Other Sources –> Blank Query

• The above lets the Power Query window opens, now from Home Tab –> Advanced Editor –> And paste the following M-Code by removing whatever you see, and press Done

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"New Material Number", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Old Material Number"}, {{"New Material Number", each Text.Combine([New Material Number],","), type number}}),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Grouped Rows", {{"New Material Number", type text}}, "en-US"), "New Material Number", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"New Material Number.1", "New Material Number.2", "New Material Number.3"})
in
    #"Split Column by Delimiter"

• Lastly, to import it back to Excel –> Click on Close & Load or Close & Load To –> The first one which clicked shall create a New Sheet with the required output while the latter will prompt a window asking you where to place the result.

Also since you have tagged Excel-Formulas, then you can do the following using LAMBDA( ) helper function MAKEARRAY( )

• Formula used in cell D1

=LET(
     α,A2:A7,
     σ,COUNTIF(α,α),
     ß,MAX(σ),
     Ω,ROWS(α),
     VSTACK(HSTACK($A$1,$B$1&" "&SEQUENCE(1,ß)),
     IFERROR(UNIQUE(HSTACK(α,MAKEARRAY(Ω,ß,
     LAMBDA(r,c,INDEX(FILTER(B2:B7,α=INDEX(α,r)),c))))),"")))