Excel function to split string and transpose-CodePudding

I have a requirement where I need to split a string '/' separated and transpose into rows and get data from another column. I need an Excel function. I am using Office 2016/ 365.

If I add a new project or new name it will reflect in output.

Col A  Names
------ -------
Proj1   Nam1/Nam2/Nam3
Proj2   Nam3/Nam5
Proj3   Nam2/Nam4
Proj4   Nam1/Nam5/Nam7

Expected Output
Nam1   Proj1
Nam1   Proj4
Nam2   Proj1
Nam2   Proj3  .. and so on
If above is not possible then maybe I can have something like below and then use PIVOT table.

colA   ColB  ColC   ColD  ColE ......
Nam1   Proj1 Proj4
Nam2   Proj1 Proj3

CodePudding user response：

Just because you stated that Office 365 is an option:

=LET(
    p,A1:A4,
    m,MAX(BYROW(B1:B4,LAMBDA(z,LEN(z)-LEN(SUBSTITUTE(z,"/","")) 1))),
    n,TOCOL(MAKEARRAY(ROWS(p),m,LAMBDA(a,b,IFERROR(INDEX(TEXTSPLIT(INDEX(B1:B4,a),"/"),b),"")))),
    o,HSTACK(n,INDEX(p,SEQUENCE(m*ROWS(p),,1,1/m))),
    SORT(FILTER(o,n<>"")))