Transposing a column to several rows
 

I have a large file with many rows; let's say it looks like this:
start
data1
data2
data3
data4
start
data1
data2
start
data1
data2
data3
....
What I want to do is transpose the column to a row, and then
each time the word START appears, start a new row. So the above
would look like this:
start data1 data2 data3 data4
start data1 data2
start data1 data2 data3
....

Any ideas?

Thanks in advance,
Scott

Make sure you make a backup copy of your data before using the code I just
posted

" wrote:

I have a large file with many rows; let's say it looks like this:
start
data1
data2
data3
data4
start
data1
data2
start
data1
data2
data3
....
What I want to do is transpose the column to a row, and then
each time the word START appears, start a new row. So the above
would look like this:
start data1 data2 data3 data4
start data1 data2
start data1 data2 data3
....

Any ideas?

Thanks in advance,
Scott

Didn't really test this.
Assumes "start" is really the text that starts a new line. If it's
something else, change the code reference to "start"
Assumes that all the columns to the right of your data are empty


Right click on the sheet tab, choose View Code
InsertModule
Paste this code
Go back to Excel, select all your data
Use ToolsMacroMacros and select TransposeData from the list
Click on OK

Sub TransposeData()
Dim rng As Range
Dim rngTgt As Range
Dim intRow As Integer
Dim intCol As Integer
Dim intDataCount As Integer

intRow = 0
intCol = 2
For Each rng In Selection
If rng.Text = "start" Then
intRow = intRow + 1
intCol = 2
Else
intCol = intCol + 1
End If
Cells(intRow, intCol).Value = rng.Text
Next

End Sub

" wrote:

I have a large file with many rows; let's say it looks like this:
start
data1
data2
data3
data4
start
data1
data2
start
data1
data2
data3
....
What I want to do is transpose the column to a row, and then
each time the word START appears, start a new row. So the above
would look like this:
start data1 data2 data3 data4
start data1 data2
start data1 data2 data3
....

Any ideas?

Thanks in advance,
Scott

An alternative ...(but test first!) ...

Sub Transpose()

i = Cells(Rows.Count, "A").End(xlUp).Row
Do
n = 0
Do
n = n + 1
Loop While Cells(i - n, 1) < "Start"

Set rng = Cells(i - n + 1, 1).Resize(n, 1)
rng.Copy
Cells(i - n, 2).Resize(1, n).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
rng.EntireRow.Select
Selection.Delete Shift:=xlUp
i = i - n - 1

Loop While i 1
End Sub

HTH

" wrote:

I have a large file with many rows; let's say it looks like this:
start
data1
data2
data3
data4
start
data1
data2
start
data1
data2
data3
....
What I want to do is transpose the column to a row, and then
each time the word START appears, start a new row. So the above
would look like this:
start data1 data2 data3 data4
start data1 data2
start data1 data2 data3
....

Any ideas?

Thanks in advance,
Scott

Ok Duke - thanks so much! Your script worked fine, however it stops
with a run-time error '1004' after just processing 895 rows of a
30042-row spreadsheet. It works beautifully up to that point,
transposing nicely. When I click on "debug", it points to the
Cells(intRow, intCol).Value = rng.Text line.

Any ideas at what is breaking it?
Scott