3

How do I transpose data in columns to rows?

If the data is:

Joe  
some city  
some state  
Bob  
no city  
no state  

and I use Paste Special » Transpose, I get

Joe some city some state Bob no city no state  

What I want is:

Joe some city some state  
Bob no city no state  

I do not know VBA.

3

3 Answers 3

1

We're gonna learn you some VBA this morning and you're going to feel good about that! Put on your rocket boots and get ready to sail!

This macro will do it for you if it's all in column A

Hit Alt + F11 to bring up VBA editor. Right click insert - module. Paste the code below in the module and to go debug - compile project and hit F5 to run it. Easy breezy.

Sub transposedelete()

Dim rownum As Long
Dim colnum As Long
Dim data, result
colnum = 1

Application.ScreenUpdating = False

'check if the data on the sheet start where the code expects it
If Range("a1") = "" Then Exit Sub


    ' define data range
    With Range("a1", Cells(Rows.Count, "a").End(xlUp)).Resize(, 2)
    data = .Value
    numrows = UBound(data)

    'loop it
    For rownum = 2 To numrows

    Range((Cells(rownum, colnum)), (Cells(rownum + 1, colnum))).Copy
    ' transpose
    Cells(rownum - 1, colnum + 1).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True

    'delete rows (this could be cleaner)
    Rows(rownum).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Rows(rownum).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp


    Next

    End With

    Application.ScreenUpdating = True

End Sub

Easy as 1, 2, 3:

1.
enter image description here
2.
enter image description here
3.
enter image description here

See now that wasn't difficult or intimidating, was it? And now you have a trick up your sleeve! Learning is great fun!

0

update: Excel 2007 and later actually does have a one-column-to-multiple-rows feature. Read about it here. For the reason the transpose feature isn't doing what you want, see below:

In order for transposition to work the way you want, your original data would need to be arranged like this:

Joe        Bob
city1      city2
state1     state2

Transposition changes rows to columns and vice versa. The way your information is formatted in your example, it's all one long column and excel changes it to one long row. It has no way of knowing that you want it to treat your column with six items as two blocks of three items each.

2
  • 2
    The article you link to is how to split one column to multiple columns, not rows.
    – mischab1
    Mar 9, 2012 at 19:53
  • When you put multiple columns side-by-side, you've created rows. Depending on how the data in the single column are laid out, you might have to transpose after breaking into multiple columns.
    – octern
    Mar 9, 2012 at 21:48
0

This answer uses the data from How to convert row format data into column format?, which was closed as a duplicate of this question.  So, this answer does answer this question, with trivial modifications (i.e., ignore the parts of this answer that talk about data labels.


First of all, I’ll assume that your data are what you appear to be saying:

                                               

Create a new sheet and enter column headings that correspond to your three data labels:

                                               

(You can do this by copying cells A1:A3 from Sheet1 and transposing them, but, for just three, it’s probably easier just to type them in manually.)  Now, note that, every time you move one cell (i.e., one column) to the right on Sheet2, that corresponds to moving down one row on Sheet1.  And every time you move one row down on Sheet2, that corresponds to moving down three rows on Sheet1.  So, guess that =ROW()*3 + COLUMN() might be a useful formula, and enter that into cells A2:C4:

                                               

Note that the values progress in the way that we want them to.  But we want Sheet2!A2 to get its value from Sheet1!A1, so let’s subtract 7 and use the result as an offset. Enter

=OFFSET(Sheet1!$A$1, ROW()*3+COLUMN()-7, 0)

into cells A2:C4:

                                               

which is the data that you want, but with the data labels still attached.  So, to fix that, change the formula to

=SUBSTITUTE(OFFSET(Sheet1!$A$1, ROW()*3+COLUMN()-7, 0), A$1 & ": ", "")

to strip the label (from row 1 off the current column) from the cell data:

                                               

and then you can copy and paste values.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .