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.

 

[Fluff]

Another option

=HSTACK(TOCOL(IF(B3:F10<>"",A3:A10,1/0),2),TOCOL(B3:F10,1))

 

[Sufiyan97]

Thank you @HighAndWilder @djclements @alansidman and @Fluff , all solutions worked.

I have marked Fluff's as it is easy to understand for me.

 

[djclements]

You're welcome. Just a friendly word of caution for the chosen solution...

=HSTACK(TOCOL(IF(B3:F10<>"",A3:A10,1/0),2),TOCOL(B3:F10,1))

...could potentially return incorrect results if range B3:F10 includes cells with empty strings. Array1 will exclude blank cells and cells containing empty strings, whereas array2 will exclude blank cells only. To play it safe, the logical_test argument should be NOT(ISBLANK(B3:F10)), or just ISBLANK(B3:F10) with the value_if_true and value_if_false arguments reversed:

=HSTACK(TOCOL(IF(ISBLANK(B3:F10),1/0,A3:A10),2),TOCOL(B3:F10,1))

However, this method still won't remove cells with empty strings, if present.

 

[alansidman]

If you are unsure on how to implement the PQ solution, here are some videos to help you understand that approach.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

The Complete Guide to Installing Power Query - Excel Campus

Learn what version you need to get Power Query and instructions on how to install this free add-in from Microsoft.

www.excelcampus.com

- Follow this link for an introduction to Power Query functionality.

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

Learn how this FREE Excel add-in from Microsoft called Power Query will help transform your data into a format that is ready for pivot tables, reports and more.

www.excelcampus.com

- Follow this link for a video which demonstrates how to use Power Query code provided.

Power Query - How To Paste Code (video) - Excel Solutions

Power Query is great at solving data transformation and consolidation issues. But there are still loads of Excel users who have never used Power Query yet – and sometimes when they’re given some Power Query code to answer their question, they don’t know what to do with it. Here’s a short video...

excel.solutions

 

[Sufiyan97]

Thanks Alan, thanks for PQ resources, I will definitely look and learn.

I’m already familiar with how to use Power Query codes, but I’m excited to dive deeper and explore more advanced techniques using the resources you've provided.

here is result with code you have provided

Excel Practice 12.17.24.xlsx
	A	B	C	D
1	Name	Cources
2	Name 1	Course1
3	Name 1	Course2
4	Name 2	Course1
5	Name 3	Course1
6	Name 3	Course2
7	Name 3	Course3
8	Name 4	Course1
9	Name 4	Course2
10	Name 4	Course3
11	Name 4	Course4
12	Name 4	Course5
13	Name 5	Course1
14	Name 5	Course2
15
Table3

 

[Fluff]

Glad we could help & thanks for the feedback.