Transpose Columns to Rows and Repeat with each Row

[sushias]

Hi,

I have a table of names as columns and skills matrix as rows for each name. However, I am now trying to move each column (name) to row and repeat all of the skills matrix for each name eg shown below. I have Names listed from Col B to Col W and may keep on increasing and changing and have skills listed in Row 2 to Row 13 for Each name and may increase

Is there a macro that can be created which looks at the total number of rows and columns as the table can be dynamic and can change the number of names.

Current

	Tony	Ron	Kumar
Training	14	14	7
Test	5	4	9

<tbody>
</tbody>

To

Name	Skill	Score
Tony	Training	14
Tony	Test	5
Ron	Training	14
Ron	Test	4
Kumar	Training	7
Kumar	Test	9

<tbody>
</tbody>

 

[sandy666]

with Power Query (Get&Transform)

Skills	Tony	Ron	Kumar		Skills	Name	Score
Training	14​	14​	7​		Test	Kumar	9​
Test	5​	4​	9​		Training	Kumar	7​
					Test	Ron	4​
					Training	Ron	14​
					Training	Tony	14​
					Test	Tony	5​

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Skills"}, "Attribute", "Value"),
    Sort = Table.Sort(Unpivot,{{"Attribute", Order.Ascending}}),
    Rename = Table.RenameColumns(Sort,{{"Attribute", "Name"}, {"Value", "Score"}})
in
    Rename[/SIZE]