Transpose Data and Expand Names Based on Courses Pending

[Sufiyan97]

I have below row data shown as below in sample and I want a formula to populate expected results shown

Book5
	A	B	C	D	E	F	G	H	I	J	K
1	Row Data								Expected Result
2	Name	Course Name 1	Course Name 2	Course Name 3	Course Name 4	Course Name 5			Name	Courses
3	Name 1	Course1	Course2						Name 1	Course1
4	Name 2	Course1							Name 1	Course2
5	Name 3	Course1	Course2	Course3					Name 2	Course1
6	Name 4	Course1	Course2	Course3	Course4	Course5			Name 3	Course1
7	Name 5	Course1	Course2						Name 3	Course2
8									Name 3	Course3
9									Name 4	Course1
10									Name 4	Course2
11									Name 4	Course3
12									Name 4	Course4
13									Name 4	Course5
14									Name 5	Course1
15									Name 5	Course2
16
Sheet1

 

[HighAndWilder]

I have below row data shown as below in sample and I want a formula to populate expected results shown

Book5
	A	B	C	D	E	F	G	H	I	J	K
1	Row Data								Expected Result
2	Name	Course Name 1	Course Name 2	Course Name 3	Course Name 4	Course Name 5			Name	Courses
3	Name 1	Course1	Course2						Name 1	Course1
4	Name 2	Course1							Name 1	Course2
5	Name 3	Course1	Course2	Course3					Name 2	Course1
6	Name 4	Course1	Course2	Course3	Course4	Course5			Name 3	Course1
7	Name 5	Course1	Course2						Name 3	Course2
8									Name 3	Course3
9									Name 4	Course1
10									Name 4	Course2
11									Name 4	Course3
12									Name 4	Course4
13									Name 4	Course5
14									Name 5	Course1
15									Name 5	Course2
16
Sheet1

There is probably a more concise way though.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Row Data								Expected Result
2	Name	Course Name 1	Course Name 2	Course Name 3	Course Name 4	Course Name 5			Name	Courses
3	Name 1	Course1	Course2						Name 1	Course1
4	Name 2	Course1							Name 1	Course2
5	Name 3	Course1	Course2	Course3					Name 2	Course1
6	Name 4	Course1	Course2	Course3	Course4	Course5			Name 3	Course1
7	Name 5	Course1	Course2						Name 3	Course2
8									Name 3	Course3
9									Name 4	Course1
10									Name 4	Course2
11									Name 4	Course3
12									Name 4	Course4
13									Name 4	Course5
14									Name 5	Course1
15									Name 5	Course2
Sheet1

Cell Formulas
Range	Formula
I3:J15	I3	=HSTACK(WRAPROWS(TEXTSPLIT(TEXTJOIN(",",TRUE,REPT($A$3:$A$7 & ",",BYROW($B$3:$F$7,COUNTA))),",",,TRUE),1),TRANSPOSE(TEXTSPLIT(TEXTJOIN(",",TRUE,$B$3:$F$7),",",,TRUE)))
Dynamic array formulas.

 

[alansidman]

An alternative is to use Power Query and Unpivot your data

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Cources"}})
in
    #"Renamed Columns"

 

[djclements]

Another possibility for Office 365:

=LET(
    data, A3:F7,
    labels, TAKE(data,,1),
    values, DROP(data,,1),
    unpvtλ, LAMBDA(area,TOCOL(IFS(values<>"",area),2)),
    HSTACK(unpvtλ(labels),unpvtλ(values))
)

Adjust the data range reference as needed.