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.

Header1	Header 2	Header 3	Header 4
FU	Bar	Hello	World
End	Of	Days	Cheese

<tbody>
</tbody>

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)

Header1	Header 2	Header 3	Header 4		Attribute	Value
FU	Bar	Hello	World		Header1	FU
End	Of	Days	Cheese		Header 2	Bar
					Header 3	Hello
					Header 4	World
					Header1	End
					Header 2	Of
					Header 3	Days
					Header 4	Cheese

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