How to transpose the table below into 1 (actually 2) column with t

[pemt]

Dear all,

How to transpose the table below into 1 (actually 2) column with their name
tag?
my table:
Name col1 col2 col3 col4
A 1 2 3 4
B 5 6
C 7 8 9

Transpose into
A 1
A 2
A 3
A 4
B 5
B 6
C 7
C 8
C 9


Thanks,

pemt

 

[Max]

Posted this response in your earlier thread:
---------------------------
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