Transpose Columns to Rows by blank

S

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?
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
Re: Transpose Columns to Rows by blank help!!

Use

Code: 
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row

You're looking at the column of values to determine the row containing the last value.
 
Last edited:
Upvote 0
Re: Transpose Columns to Rows by blank help!!

Thank you but that still doesn't resolve the single row issue. It transposes well until it hits the value in just a single row because the .End(x1Down) only reads values in 2 or more consecutive rows.


Special-K99 said:
Use

Code: 
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row

You're looking at the column of values to determine the row containing the last value.
Click to expand...
 
Upvote 0
Re: Transpose Columns to Rows by blank help!!

There's nothing wrong with this at all.
You have 19 rows in column B.
This

Code: 
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row

returns 19.
So now you know how many rows are in your table.

You will of course have to manipulate the value in column A by retaining the LAST column A value so when you come across a blank in column A you just you use the previous value which is the value you've retained. Similarly you need to check when both A and B are blank.

QUESTION: Is there any reason why you are retaining numeroues blank rows between the names? They are not consistent with each name change. They seem to reflect the number of values in column B that were in the original table.
 
Last edited:
Upvote 0
Re: Transpose Columns to Rows by blank help!!

This works but does not produce any blank rows between names
Input on Sheet1
Output on Sheet2

Rich (BB code): 
Sub k1()
lasta = ""
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To lastrow
If Worksheets("Sheet1").Cells(i, 1) <> lasta Then
j = j + 1
Worksheets("Sheet2").Cells(j, 1) = Worksheets("Sheet1").Cells(i, 1)
Worksheets("Sheet2").Cells(j, 2) = Worksheets("Sheet1").Cells(i, 2)
k = 2
Else
k = k + 1
Worksheets("Sheet2").Cells(j, k) = Worksheets("Sheet1").Cells(i, 2)
End If
Next i
End Sub

It also uses the "lastrow" line I exactly as I posted earlier.

This modification adds the blank lines exactly as you've specified.

Rich (BB code): 
Sub k1()
lasta = ""
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
k = 2
For i = 1 To lastrow
If Worksheets("Sheet1").Cells(i, 1) <> lasta Then
j = j + k - 1
Worksheets("Sheet2").Cells(j, 1) = Worksheets("Sheet1").Cells(i, 1)
Worksheets("Sheet2").Cells(j, 2) = Worksheets("Sheet1").Cells(i, 2)
k = 2
Else
k = k + 1
Worksheets("Sheet2").Cells(j, k) = Worksheets("Sheet1").Cells(i, 2)
End If
Next i
End Sub
 
Last edited:
Upvote 0
Re: Transpose Columns to Rows by blank help!!

=Special-K99;4871146]There's nothing wrong with this at all.
You have 19 rows in column B.
This

Code: 
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row

returns 19.
So now you know how many rows are in your table.

You will of course have to manipulate the value in column A by retaining the LAST column A value so when you come across a blank in column A you just you use the previous value which is the value you've retained. Similarly you need to check when both A and B are blank.

QUESTION: Is there any reason why you are retaining numeroues blank rows between the names? They are not consistent with each name change. They seem to reflect the number of values in column B that were in the original table.
Click to expand...


The code returns the following table. It is not transposing correctly when it gets to Jack Willow. Also I am keeping the spaces because there are names such as Anna Kendrick that have no values, but I want to retain them in the list. This data sheet has over 1000 entries, and there are many names that have no values.
[TABLE="class: grid, width: 376"]
<tbody>[TR]
[TD="width: 33%"]Andrea Anderson
[/TD]
[TD="width: 13%"]85.98
[/TD]
[TD="width: 13%"]97.62
[/TD]
[TD="width: 15%"]100.00
[/TD]
[TD="width: 15%"]100.00
[/TD]
[TD="width: 13%"][/TD]
[/TR]
[TR]
[TD="width: 33%"][/TD]
[TD="width: 13%"][/TD]
[TD="width: 13%"][/TD]
[TD="width: 15%"][/TD]
[TD="width: 15%"][/TD]
[TD="width: 13%"][/TD]
[/TR]
[TR]
[TD="width: 33%"][/TD]
[TD="width: 13%"][/TD]
[TD="width: 13%"][/TD]
[TD="width: 15%"][/TD]
[TD="width: 15%"][/TD]
[TD="width: 13%"][/TD]
[/TR]
[TR]
[TD="width: 33%"][/TD]
[TD="width: 13%"][/TD]
[TD="width: 13%"][/TD]
[TD="width: 15%"][/TD]
[TD="width: 15%"][/TD]
[TD="width: 13%"][/TD]
[/TR]
[TR]
[TD="width: 33%"][/TD]
[TD="width: 13%"][/TD]
[TD="width: 13%"][/TD]
[TD="width: 15%"][/TD]
[TD="width: 15%"][/TD]
[TD="width: 13%"][/TD]
[/TR]
[TR]
[TD="width: 33%"]Jane Austin
[/TD]
[TD="width: 13%"]89.81
[/TD]
[TD="width: 13%"]78.70
[/TD]
[TD="width: 15%"]100.00
[/TD]
[TD="width: 15%"][/TD]
[TD="width: 13%"][/TD]
[/TR]
[TR]
[TD="width: 33%"][/TD]
[TD="width: 13%"][/TD]
[TD="width: 13%"][/TD]
[TD="width: 15%"][/TD]
[TD="width: 15%"][/TD]
[TD="width: 13%"][/TD]
[/TR]
[TR]
[TD="width: 33%"][/TD]
[TD="width: 13%"][/TD]
[TD="width: 13%"][/TD]
[TD="width: 15%"][/TD]
[TD="width: 15%"][/TD]
[TD="width: 13%"][/TD]
[/TR]
[TR]
[TD="width: 33%"][/TD]
[TD="width: 13%"][/TD]
[TD="width: 13%"][/TD]
[TD="width: 15%"][/TD]
[TD="width: 15%"][/TD]
[TD="width: 13%"][/TD]
[/TR]
[TR]
[TD="width: 33%"]Jack Willow
[/TD]
[TD="width: 13%"]94.32
[/TD]
[TD="width: 13%"][/TD]
[TD="width: 15%"][/TD]
[TD="width: 15%"][/TD]
[TD="width: 13%"]90.91
[/TD]
[/TR]
[TR]
[TD="width: 33%"][/TD]
[TD="width: 13%"][/TD]
[TD="width: 13%"][/TD]
[TD="width: 15%"][/TD]
[TD="width: 15%"][/TD]
[TD="width: 13%"][/TD]
[/TR]
[TR]
[TD="width: 33%"]Anna Kendrick
[/TD]
[TD="width: 13%"][/TD]
[TD="width: 13%"][/TD]
[TD="width: 15%"][/TD]
[TD="width: 15%"][/TD]
[TD="width: 13%"][/TD]
[/TR]
[TR]
[TD="width: 33%"][/TD]
[TD="width: 13%"][/TD]
[TD="width: 13%"][/TD]
[TD="width: 15%"][/TD]
[TD="width: 15%"][/TD]
[TD="width: 13%"][/TD]
[/TR]
[TR]
[TD="width: 33%"]Frida Kahlo
[/TD]
[TD="width: 13%"]89.11
[/TD]
[TD="width: 13%"][/TD]
[TD="width: 15%"][/TD]
[TD="width: 15%"][/TD]
[TD="width: 13%"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Re: Transpose Columns to Rows by blank help!!

This code doesn't seem to give me any output


Special-K99 said:
This works but does not produce any blank rows between names
Input on Sheet1
Output on Sheet2

Rich (BB code): 
Sub k1()
lasta = ""
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To lastrow
If Worksheets("Sheet1").Cells(i, 1) <> lasta Then
j = j + 1
Worksheets("Sheet2").Cells(j, 1) = Worksheets("Sheet1").Cells(i, 1)
Worksheets("Sheet2").Cells(j, 2) = Worksheets("Sheet1").Cells(i, 2)
k = 2
Else
k = k + 1
Worksheets("Sheet2").Cells(j, k) = Worksheets("Sheet1").Cells(i, 2)
End If
Next i
End Sub

It also uses the "lastrow" line I exactly as I posted earlier.

This modification adds the blank lines exactly as you've specified.

Rich (BB code): 
Sub k1()
lasta = ""
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
k = 2
For i = 1 To lastrow
If Worksheets("Sheet1").Cells(i, 1) <> lasta Then
j = j + k - 1
Worksheets("Sheet2").Cells(j, 1) = Worksheets("Sheet1").Cells(i, 1)
Worksheets("Sheet2").Cells(j, 2) = Worksheets("Sheet1").Cells(i, 2)
k = 2
Else
k = k + 1
Worksheets("Sheet2").Cells(j, k) = Worksheets("Sheet1").Cells(i, 2)
End If
Next i
End Sub
Click to expand...
 
Upvote 0
Re: Transpose Columns to Rows by blank help!!

Sorry it does work, I had to select the data first. Thank you!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
hel separating
Replies
6
Views
730
Fluff
Fluff
J
  • Question Question
help find not 'exact'
Replies
1
Views
688
mumps
mumps
C
  • Question Question
Dynamic IF calculations
Replies
6
Views
823
Fluff
Fluff
J
  • Question Question
find dupes and hide non dupes
Replies
3
Views
692
Joe4
Joe4
J
  • Question Question
VBA Macro for Identifying Background Color
Replies
5
Views
2K
Yongle
Yongle

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

Share this page

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