Help sorting data header cell, header cell etc

[Martynrbell]

Hi All,

I need to get data in a table to then list using the clumn header then the cell, per row.[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Header1[/TD]
[TD]Header 2[/TD]
[TD]Header 3[/TD]
[TD]Header 4[/TD]
[/TR]
[TR]
[TD]FU[/TD]
[TD]Bar[/TD]
[TD]Hello[/TD]
[TD]World[/TD]
[/TR]
[TR]
[TD]End[/TD]
[TD]Of [/TD]
[TD]Days[/TD]
[TD]Cheese[/TD]
[/TR]
</tbody>[/TABLE]

using the table above as an example to display the below

Header1 "FU"
Header2 "Bar"
Header3 "Hello"
Header4 "World"

Header1 "End"
Header2 "Of"
Header3 "Days"
Header4 "Cheese"

The column range will be about 10 columns and the rows will be about 68 however i would like to configure it to different table ranges.

Can anyone help me sort this out?
Cheers

Martyn

 

[DanteAmor]

Your data must be start on sheet1 in cell A1

Try this macro:

Sub Sorting_Data()
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
sh2.Cells.ClearContents
uc = sh1.Cells(1, Columns.Count).End(xlToLeft).Column
For i = 2 To sh1.Range("A" & Rows.Count).End(xlUp).Row
    sh2.Range("A" & Rows.Count).End(xlUp)(2).Resize(uc).Value = Application.Transpose(sh1.Range("A1").Resize(1, uc).Value)
    sh2.Range("B" & Rows.Count).End(xlUp)(2).Resize(uc).Value = Application.Transpose(sh1.Range("A" & i).Resize(1, uc).Value)
Next
End Sub

 

[sandy666]

try PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Header1[/td][td=bgcolor:#5B9BD5]Header 2[/td][td=bgcolor:#5B9BD5]Header 3[/td][td=bgcolor:#5B9BD5]Header 4[/td][td][/td][td=bgcolor:#70AD47]Attribute[/td][td=bgcolor:#70AD47]Value[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]FU[/td][td=bgcolor:#DDEBF7]Bar[/td][td=bgcolor:#DDEBF7]Hello[/td][td=bgcolor:#DDEBF7]World[/td][td][/td][td=bgcolor:#E2EFDA]Header1[/td][td=bgcolor:#E2EFDA]FU[/td][/tr]

[tr=bgcolor:#FFFFFF][td]End[/td][td]Of[/td][td]Days[/td][td]Cheese[/td][td][/td][td]Header 2[/td][td]Bar[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Header 3[/td][td=bgcolor:#E2EFDA]Hello[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]Header 4[/td][td]World[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Header1[/td][td=bgcolor:#E2EFDA]End[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]Header 2[/td][td]Of[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Header 3[/td][td=bgcolor:#E2EFDA]Days[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]Header 4[/td][td]Cheese[/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value")
in
    Unpivot[/SIZE]

 

[Martynrbell]

Thanks. I will give both of these a try

 

[sandy666]

You are welcome

 

[DanteAmor]

Thanks. I will give both of these a try

Ok, let me know if you have any doubt.