How to convert column into row

Question

I hv this format in excel (column):-

enter image description here

I wish to convert it into row:-

enter image description here

Is it possible ?

Thanks.

Its possible .. Have u tried to code ? – Sathish Kothandam Jan 23 '14 at 04:41 — Sathish Kothandam, Jan 23 '14 at 04:41
Code ? You mean VBA ? I do not know how to do that. – Bob Jan 23 '14 at 06:05 — Bob, Jan 23 '14 at 06:05
Unpivot if you are using excel 2013 – vasin1987 Jan 23 '14 at 10:32 — vasin1987, Jan 23 '14 at 10:32

score 0 · Answer 1 · edited May 23 '17 at 10:25

0

Unpivot (eg as described here), drill down on the intersection of the two Grand Totals and move cells with blanks in ColumnB but values in third up to row two. Delete rows with blanks in ColumD. Change labels to suit.

edited May 23 '17 at 10:25

Community

1
1

answered Jan 23 '14 at 05:15

pnuts

58,317
11
87
139

score 0 · Answer 2 · answered Jan 23 '14 at 07:25

0

It's simple with offsets:

Following your image you may write:

In cell B7: =$A$3
In cell C7: =OFFSET($A$3,0,(ROW(A7)-7)*2+1)
In cell D7: =OFFSET($A$3,0,(ROW(A7)-6)*2)

Then fill rows as necessary.

answered Jan 23 '14 at 07:25

hstay

1,439
1
11
20

score 0 · Answer 3 · answered Mar 24 '14 at 16:57

You can also unpivot using my unpivot add-in, after unmerging the top cells. You can use the below code to unmerge your top row first:

Sub UnmergeTopRow()

Dim lLastcol As Long, lcolLoop As Long

lLastcol = Cells(1, Columns.Count).End(xlUp).Column

Range("B1", Cells(1, lLastcol)).UnMerge

With Range("B1", Cells(1, lLastcol))
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC[-1]"
    .Value = .Value
End With

End Sub

How to convert column into row

3 Answers3