how to transpose multiple rows into one column with row labeling?
 

Dear all,

I need to convert the following table:
a 1 2 3
b 7 8
c 4 5 6 9

into:
a 1
a 2
a 3
b 7
b 8
c 4
c 5
c 6
c 9

how to do it?
thanks,

pemt

how to transpose multiple rows into one column with row labeling?
 

Try this macro:

==========
Sub ReOrganize()
'JBeaucaire (11/4/2009)
'Turns row data into columnar data
Dim LR As Long, i As Long, r As Long, c As Long, v As Long
Application.ScreenUpdating = False

LR = Range("A" & Rows.Count).End(xlUp).Row
i = 1

Do Until Range("A" & i) = ""
If Range("C" & i) < "" Then
c = Cells(i, Columns.Count).End(xlToLeft).Column
v = i
For r = 3 To c
i = i + 1
Rows(i).Insert xlShiftDown
Range("A" & i) = Range("A" & i - 1)
Range("B" & i) = Cells(v, r)
Next r
End If
i = i + 1
Loop

Range("C1", Cells(Rows.Count, Columns.Count)).ClearContents
Application.ScreenUpdating = True
End Sub
==========

--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.


"pemt" wrote:

Dear all,

I need to convert the following table:
a 1 2 3
b 7 8
c 4 5 6 9

into:
a 1
a 2
a 3
b 7
b 8
c 4
c 5
c 6
c 9

how to do it?
thanks,

pemt

how to transpose multiple rows into one column with row labeli
 

JBeaucai

Thanks a lot!
It works well. Is it possible to put transposed data in a new sheet?

pemt

"JBeaucaire" wrote:

Try this macro:

==========
Sub ReOrganize()
'JBeaucaire (11/4/2009)
'Turns row data into columnar data
Dim LR As Long, i As Long, r As Long, c As Long, v As Long
Application.ScreenUpdating = False

LR = Range("A" & Rows.Count).End(xlUp).Row
i = 1

Do Until Range("A" & i) = ""
If Range("C" & i) < "" Then
c = Cells(i, Columns.Count).End(xlToLeft).Column
v = i
For r = 3 To c
i = i + 1
Rows(i).Insert xlShiftDown
Range("A" & i) = Range("A" & i - 1)
Range("B" & i) = Cells(v, r)
Next r
End If
i = i + 1
Loop

Range("C1", Cells(Rows.Count, Columns.Count)).ClearContents
Application.ScreenUpdating = True
End Sub
==========

--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.


"pemt" wrote:

Dear all,

I need to convert the following table:
a 1 2 3
b 7 8
c 4 5 6 9

into:
a 1
a 2
a 3
b 7
b 8
c 4
c 5
c 6
c 9

how to do it?
thanks,

pemt