Transpose Columns to Rows by blank

snehaa

New Member
Joined
Jul 18, 2017
Messages
6
<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica} p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica; min-height: 14.0px} span.Apple-tab-span {white-space:pre} </style>I need to transpose vertical data from column B to horizontal data.

My table looks like this:


<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 10.0px Arial}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 10.0px Arial}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 10.0px Arial; min-height: 11.0px}table.t1 {border-collapse: collapse}td.td1 {border-style: solid; border-width: 1.0px 1.0px 1.0px 1.0px; border-color: #cbcbcb #cbcbcb #cbcbcb #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}</style>
[TABLE="class: t1"]
<tbody>[TR]
[TD="class: td1"]Andrea Anderson[/TD]
[TD="class: td1"]85.98[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]97.62[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]100.00[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]100.00[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"]Jane Austin[/TD]
[TD="class: td1"]89.81[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]78.70[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]100.00[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"]Jack Willow[/TD]
[TD="class: td1"]94.32[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"]Anna Kendrick[/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"]Frida Kahlo[/TD]
[TD="class: td1"]90.91[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]0.00[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]88.54[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]76.96[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]94.32[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]89.11[/TD]
[/TR]
</tbody>[/TABLE]


And I want it to look like this:


<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 10.0px Arial}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 10.0px Arial}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 10.0px Arial; min-height: 11.0px}table.t1 {border-collapse: collapse}td.td1 {border-style: solid; border-width: 0.8px 0.8px 0.8px 0.8px; border-color: #000000 #000000 #000000 #000000; padding: 0.0px 5.0px 0.0px 5.0px}</style>
[TABLE="class: t1"]
<tbody>[TR]
[TD="class: td1"]Andrea Anderson[/TD]
[TD="class: td1"]85.98[/TD]
[TD="class: td1"]97.62[/TD]
[TD="class: td1"]100.00[/TD]
[TD="class: td1"]100.00[/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"]Jane Austin[/TD]
[TD="class: td1"]89.81[/TD]
[TD="class: td1"]78.70[/TD]
[TD="class: td1"]100.00[/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"]Jack Willow[/TD]
[TD="class: td1"]94.32[/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"]Anna Kendrick[/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"]Frida Kahlo[/TD]
[TD="class: td1"]90.91[/TD]
[TD="class: td1"]0.00[/TD]
[TD="class: td1"]88.54[/TD]
[TD="class: td1"]76.96[/TD]
[TD="class: td1"]94.32[/TD]
[TD="class: td1"]89.11[/TD]
[/TR]
</tbody>[/TABLE]

I am using the following code:


Code:
> Sub Transpose()
>     Dim t As Range, u As Range
>     c = ActiveCell.Column
>     fr = ActiveCell.Row
>     lr = Cells(Rows.Count, c).End(xlUp).Row
>     r = fr
>     Do
>         Set t = Cells(r, c)
>         Set u = t.End(xlDown)
>         Range(t, u).Copy
>         t.Offset(, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
>         r = u.End(xlDown).Row
>     Loop While r < lr
>    Application.CutCopyMode = False End Sub

The problem is .End(x1Down) doesn't work because there are single rows of data. Is there a solution for this?
 
Can you use something like this? Assume your for Person A is 1,2,3, and 4 in B1:B4. Assume you will place formula in D1. Since there are 4 numbers, you will need to use your mouse to select all the cells D1:G1. Enter the formula =transpose (D1:G1). You need to press Cntrl+Shift+Enter at the same time to enter this. Then you are done. This formula produces horizontal answers for a vertical range and vice versa

[TABLE="width: 512"]
<colgroup><col span="8"></colgroup><tbody>[TR]
[TD]a[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]a[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Forum statistics

Threads
1,223,249
Messages
6,171,031
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top