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
---
"pemt" wrote:
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