Moving the columns from horizontal to vertical

[dankar]

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]FIRST NAME
[/TD]
[TD]LAST NAME
[/TD]
[TD]DATE
[/TD]
[TD]PHONE#
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ALLAN
[/TD]
[TD]SMITH
[/TD]
[TD]05/30/2019
[/TD]
[TD]4701325555
[/TD]
[/TR]
</tbody>[/TABLE]

To become like this:


[TABLE="width: 200"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]FIRST NAME
[/TD]
[TD]ALLAN
[/TD]
[/TR]
[TR]
[TD]LAST NAME
[/TD]
[TD]SMITH
[/TD]
[/TR]
[TR]
[TD]DATE
[/TD]
[TD]05/30/2019
[/TD]
[/TR]
[TR]
[TD]PHONE#
[/TD]
[TD]4701325555
[/TD]
[/TR]
</tbody>[/TABLE]


Hi,

1. I have an excel file with over than 200 columns, I want to copy the heading to vertical position.

2. If possible also to copy the result also.

is there an easy way to do this. thanks

 

[Joe4]

This is pretty easy to do. Let's work with your example (and let's say the data is in cella A1:E2).
1. Select the entire range (A1:E2)
2. Select Copy (CTRL + C)
3. Select cell A3
4. Right-click, and select Paste Special --> Transpose --> OK
5. Delete rows 1 and 2

That's it!
It will work for 200 columns just as easily as it does for 5

 

[alansidman]

An alternative option is to bring the data into Power Query and unpivot the columns.

Here is the Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value")
in
    #"Unpivoted Columns"

[table="class:thin_grid"]
[tr][td]v[/td]
[td="bgcolor:#ECF0F0, align:center"]A[/td]
[td="bgcolor:#ECF0F0, align:center"]B[/td]
[/tr][tr][td="bgcolor:#ECF0F0, align:center"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Attribute[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Value[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]ID[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]FIRST NAME[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]ALLAN[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]LAST NAME[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]SMITH[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]DATE[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]5/30/2019[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]6[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]PHONE#[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]4701325555[/td]
[/tr]
[/table]

 

[dankar]

Thank you all so much!

 

[Joe4]

You are welcome.