Excel transpose and organize

Solution 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]

Solution 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)