Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
share|improve this question

migrated from stackoverflow.com Feb 25 '10 at 8:12

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

add comment

4 Answers

up vote 4 down vote accepted

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]
share|improve this answer
    
awesome, that did the trick thanks a lot –  Daniel Feb 25 '10 at 0:42
add comment

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)

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.