Pivot or Other Solution required to transform the data

[aamaramaster]

Hi, I attached a photo for a data set I have called" Original Data" where I want to create a Pivot table or any other simple solution to create a new data table as show in the attached photo below:

 

[hagia_sofia]

Hello, one option could be:

=LET(
ID,A5:A22,
names,B5:B22,
u,UNIQUE(ID),
t,IFNA(DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,TRANSPOSE(UNIQUE(FILTER(names,ID=y)))))),1),""),
HSTACK(u,t))

 

[Fluff]

Hi & welcome to MrExcel.
Another option

=LET(u,UNIQUE(FILTER(A2:A100,A2:A100<>"")),m,MAX(COUNTIFS(A:A,u)),REDUCE(HSTACK("Order ID","Name "&SEQUENCE(,m)),u,LAMBDA(x,y,VSTACK(x,HSTACK(y,EXPAND(TOROW(FILTER(B2:B100,A2:A100=y)),,m,""))))))

 

[aamaramaster]

Hello, one option could be:

=LET(
ID,A5:A22,
names,B5:B22,
u,UNIQUE(ID),
t,IFNA(DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,TRANSPOSE(UNIQUE(FILTER(names,ID=y)))))),1),""),
HSTACK(u,t))

Thanks, it works perfectly

 

[aamaramaster]

Hi & welcome to MrExcel.
Another option

=LET(u,UNIQUE(FILTER(A2:A100,A2:A100<>"")),m,MAX(COUNTIFS(A:A,u)),REDUCE(HSTACK("Order ID","Name "&SEQUENCE(,m)),u,LAMBDA(x,y,VSTACK(x,HSTACK(y,EXPAND(TOROW(FILTER(B2:B100,A2:A100=y)),,m,""))))))

Thank you; it works, but the issue is that it retains duplicate names, as shown in the attached file. The previous formula from @hagia_sofia effectively eliminated the duplicates.

 

[Fluff]

You never dais you wanted to get rid of the duplicates.

 

[aamaramaster]

You never dais you wanted to get rid of the duplicates.

I apologize for any inconvenience, and I sincerely appreciate your support.

 

[djclements]

Another possibility with the new PIVOTBY function:

=LET(
    a, UNIQUE(FILTER(A5:B1000,A5:A1000<>"")),
    r, CHOOSECOLS(a,1),
    v, CHOOSECOLS(a,2),
    n, SEQUENCE(ROWS(a)),
    c, MMULT((n>=TOROW(n))*(r=TOROW(r)),n^0),
    p, PIVOTBY(r,c,v,CONCAT,0,0,,0),
    VSTACK(HSTACK("Order ID","Name "&DROP(TAKE(p,1),,1)),DROP(p,1))
)