Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
transpose multiple rows into one column
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
transpose multiple rows into one column
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 ======== -- "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, 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
transpose multiple rows into one column
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transpose Column to Rows | Excel Discussion (Misc queries) | |||
Transpose every 10 cells in single column to multiple rows | Excel Discussion (Misc queries) | |||
transpose multiple rows at once | Excel Discussion (Misc queries) | |||
Can you transpose 1 column into multiple rows creating a table | Excel Discussion (Misc queries) | |||
transpose a column into many rows | Excel Discussion (Misc queries) |