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
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Tony[/TD]
[TD]Ron[/TD]
[TD]Kumar[/TD]
[/TR]
[TR]
[TD]Training[/TD]
[TD]14[/TD]
[TD]14[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Test[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]

To

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Skill[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[TD]Training[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[TD]Test[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Ron[/TD]
[TD]Training[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]Ron[/TD]
[TD]Test[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Kumar
[/TD]
[TD]Training[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Kumar[/TD]
[TD]Test[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

with Power Query (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Skills[/td][td=bgcolor:#5B9BD5]Tony[/td][td=bgcolor:#5B9BD5]Ron[/td][td=bgcolor:#5B9BD5]Kumar[/td][td][/td][td=bgcolor:#70AD47]Skills[/td][td=bgcolor:#70AD47]Name[/td][td=bgcolor:#70AD47]Score[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Training[/td][td=bgcolor:#DDEBF7]

14​

[/td][td=bgcolor:#DDEBF7]

14​

[/td][td=bgcolor:#DDEBF7]

7​

[/td][td][/td][td=bgcolor:#E2EFDA]Test[/td][td=bgcolor:#E2EFDA]Kumar[/td][td=bgcolor:#E2EFDA]

9​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Test[/td][td]

5​

[/td][td]

4​

[/td][td]

9​

[/td][td][/td][td]Training[/td][td]Kumar[/td][td]

7​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Test[/td][td=bgcolor:#E2EFDA]Ron[/td][td=bgcolor:#E2EFDA]

4​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]Training[/td][td]Ron[/td][td]

14​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Training[/td][td=bgcolor:#E2EFDA]Tony[/td][td=bgcolor:#E2EFDA]

14​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]Test[/td][td]Tony[/td][td]

5​

[/td][/tr]
[/table]

[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]