Excel transpose and organize

Question

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

Answer 1

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
• [menu] Insert -> Module
• Paste the macro in (should be in 'Module1')
• Run (green arrow). Or... close and tools->macro->macros->[run]

Answer 2

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)

Answer 3

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.

Answer 4

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.