transpose multiple rows into one column

[pemt]

Dear all,

How to transpose the below table into one column (actually 2 columns) with
name?
my table:
Name col1 col2 col3 col4
A 10 20 30 40
B 50 60
C 70 80 90

transpose into:
Name col1
A 10
A 20
A 30
A 40
B 50
B 60
C 70
C 80
C 90

Thanks,

pemt

 

[JBeaucaire]

Try this macro:
========
Sub ReOrganize()
'JBeaucaire (10/23/2009)
Dim LR As Long, LC As Long
Dim FR As Long, BR As Long
Dim i As Long
Application.ScreenUpdating = False

LR = Range("A" & Rows.Count).End(xlUp).Row
Columns("A:B").Insert xlShiftToRight
Range("A1") = "Name"
Range("B1") = "Values"
FR = 2

For i = 2 To LR
LC = Cells(i, Columns.Count).End(xlToLeft).Column
Range(Cells(i, "D"), Cells(i, LC)).Copy
Range("B" & FR).PasteSpecial xlPasteValues, Transpose:=True
Range("A" & FR, "A" & FR + LC - 4) = Cells(i, "C")
FR = FR + LC - 3
Next i

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

 

[Max]

Try this play to achieve the desired transformation ..

Presume your source table/data as posted is in Sheet1,
with up to 4 cols of data for each name in col A
Data is running in row2 down

In another sheet,
Put labels in A1:B1, eg: Name, Dat
In A2: =OFFSET(Sheet1!$A$2,INT((ROWS($1:1)-1)/4),)
In B2: =OFFSET(Sheet1!$B$2,INT((ROWS($1:1)-1)/4),MOD(ROWS($1:1)-1,4))
Copy A2:B2 down as far as required to exhaust the source data, ie until
zeros appear in col A. Kill/Freeze all formulas with an "in-place" copy n
paste special as values. Then clean up by applying autofilter on col B,
filter for zeros, delete these rows/lines, remove autofilter. There, you're
done in under 60 seconds !
Modify the "4" in both formulas to suit the maximum number of data cols
Success? Celebrate it, hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik