# Excel transpose and organize

I have a bunch of rows, that I want to turn into a table.

Is there a way in Excel to convert a bunch of entries, each occupying four rows into a table, where each of the four rows is put in one of four columns ?

Basically this:

a
b
c
d

a
b
c
d

to

a b c d
a b c d
-

## migrated from stackoverflow.comFeb 25 '10 at 8:12

This question came from our site for professional and enthusiast programmers.

I don't off the top o' my head know a way to do it for ALL of them, but you can paste like this...

• select one set of abcd
• copy the cells
• edit -> paste special
• [x] the transpose button at the bottom

That will do it for THAT set of a/b/c/d. I know it's not a full answer, but if there aren't too many of these then that might work.

Here is a quick macro that will do the transpose for you, if you like. Just put everything in column "A", and it will put it all in B1,C1,D1, etc. Rows are delineated by spaces, and two spaces in a row means "stop".

fixed, should work ok for up to 255 columns now. and yes, you can use the Transpose setting in the pastespecial function in vba, but that just makes the code more complicated for the same result, so I kept it simple

Sub Macro1()
lastBlank = False
col = 2 'B
row = 1
For Each cell In Range("A:A")
v = Trim(CStr(cell))
If v = "" Then
If lastBlank = True Then Exit For
lastBlank = True
col = 2 'B
row = row + 1
Else
Cells(row, col) = v
col = col + 1
lastBlank = False
End If
Next
End Sub
• Tools->Macros->Visual Basic Editor
• Paste the macro in (should be in 'Module1')
• Run (green arrow). Or... close and tools->macro->macros->[run]
-
awesome, that did the trick thanks a lot –  Daniel Feb 25 '10 at 0:42

If anyone is still reading this blog article, here is another way to do it (just set which row the first piece of data is in using the FirstRow constant and then turn it loose)...

Sub TransposeRowsFourAtATime()
Dim X As Long, LastRow As Long, OffSetCounter As Long
Const FirstRow As Long = 2
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For X = FirstRow To LastRow Step 5
Cells(FirstRow, "A").Offset(OffSetCounter).Resize(1, 4) = _
WorksheetFunction.Transpose(Cells(X, "A").Resize(4))
OffSetCounter = OffSetCounter + 1
Next
Range(Cells(FirstRow + OffSetCounter, "A"), Cells(LastRow, "A")).Clear
End Sub

Rick Rothstein (MVP - Excel)

-

There is an excel transpose function. Type the function in to the cell where you want the transpose to start, then use the edit->fill functions to copy function sideways (and down if necessary).

There should be a way to correct the row indexing (to account for blanks) using the ADDRESS function.

-

You can enter these formulas (assuming your data starts in A1) and fill down as far as you need

B1            =IF(ISBLANK(A5),A1,"")
C1            =IF(ISBLANK(A5),A2,"")
D1            =IF(ISBLANK(A5),A3,"")
E1            =IF(ISBLANK(A5),A4,"")

Then Copy and Paste Special Values. Then sort to remove the blank lines.

-