Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Transpose Problem
Hi,
I have multiple rows of data spanning across 10 columns. This has to be transposed onto ONE column where each row of data has to be APPENED one below the other. For eg: if the data is available from C1 to L4, this data has to be transposed onto the column B from B1 to B40. Is there any simpler way than doing it manually one by one. Please help -Biman. |
#2
|
|||
|
|||
Try this macro
Sub OneColumn() '''''''''''''''''''''''''''''''''''''''''' 'Macro to tranpose rows ' 'into 1 continuous column in a new sheet ' '''''''''''''''''''''''''''''''''''''''''' 'Constructive criticism from knowledgable 'VBA programmers welcome - esp. BP! Dim OrigDataLastRow As Long Dim AllDataLastRow As Long Dim RowNdx As Long Dim ws As Worksheet Dim CopyRow As Range Set ws = ActiveWorkbook.ActiveSheet OrigDataLastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row AllDataLastRow = 1 Sheets.Add.Name = "Alldata" With Application .ScreenUpdating = False For RowNdx = 1 To OrigDataLastRow Set CopyRow = ws.Range(RowNdx & ":" & RowNdx) CopyRow.Copy Sheets("Alldata").Cells(AllDataLastRow, 1). _ PasteSpecial Transpose:=True AllDataLastRow = Sheets("Alldata"). _ Cells(Rows.Count, 1).End(xlUp).Row + 1 Next .ScreenUpdating = True .CutCopyMode = False End With End Sub --- HTH Jason Atlanta, GA -----Original Message----- Hi, I have multiple rows of data spanning across 10 columns. This has to be transposed onto ONE column where each row of data has to be APPENED one below the other. For eg: if the data is available from C1 to L4, this data has to be transposed onto the column B from B1 to B40. Is there any simpler way than doing it manually one by one. Please help -Biman. . |
#3
|
|||
|
|||
Biman wrote:
Hi, I have multiple rows of data spanning across 10 columns. This has to be transposed onto ONE column where each row of data has to be APPENED one below the other. For eg: if the data is available from C1 to L4, this data has to be transposed onto the column B from B1 to B40. Is there any simpler way than doing it manually one by one. Please help -Biman. What goes in B2, the value from C2 or D1? If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook then =ArrayReshape(C1:L4,40,1) if the answer above is D1; =ArrayReshape(C1:L4,40,1,FALSE) if the answer above is C2. Alan Beban |
#4
|
|||
|
|||
Here is another way without VBA
1. Edit | Office Clipboard | Clear All 2. Select C1:C4 | Copy 3. Repeat step 2 for each additional column 4. Select B1 5. Office Clipboard | Paste All |
#5
|
|||
|
|||
Thanks Jason. It works perfectly.
-Biman "Jason Morin" wrote: Try this macro Sub OneColumn() '''''''''''''''''''''''''''''''''''''''''' 'Macro to tranpose rows ' 'into 1 continuous column in a new sheet ' '''''''''''''''''''''''''''''''''''''''''' 'Constructive criticism from knowledgable 'VBA programmers welcome - esp. BP! Dim OrigDataLastRow As Long Dim AllDataLastRow As Long Dim RowNdx As Long Dim ws As Worksheet Dim CopyRow As Range Set ws = ActiveWorkbook.ActiveSheet OrigDataLastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row AllDataLastRow = 1 Sheets.Add.Name = "Alldata" With Application .ScreenUpdating = False For RowNdx = 1 To OrigDataLastRow Set CopyRow = ws.Range(RowNdx & ":" & RowNdx) CopyRow.Copy Sheets("Alldata").Cells(AllDataLastRow, 1). _ PasteSpecial Transpose:=True AllDataLastRow = Sheets("Alldata"). _ Cells(Rows.Count, 1).End(xlUp).Row + 1 Next .ScreenUpdating = True .CutCopyMode = False End With End Sub --- HTH Jason Atlanta, GA -----Original Message----- Hi, I have multiple rows of data spanning across 10 columns. This has to be transposed onto ONE column where each row of data has to be APPENED one below the other. For eg: if the data is available from C1 to L4, this data has to be transposed onto the column B from B1 to B40. Is there any simpler way than doing it manually one by one. Please help -Biman. . |
#6
|
|||
|
|||
Thanks Alan.
"Alan Beban" wrote: Biman wrote: Hi, I have multiple rows of data spanning across 10 columns. This has to be transposed onto ONE column where each row of data has to be APPENED one below the other. For eg: if the data is available from C1 to L4, this data has to be transposed onto the column B from B1 to B40. Is there any simpler way than doing it manually one by one. Please help -Biman. What goes in B2, the value from C2 or D1? If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook then =ArrayReshape(C1:L4,40,1) if the answer above is D1; =ArrayReshape(C1:L4,40,1,FALSE) if the answer above is C2. Alan Beban |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
repeated transpose from rows to columns with unequal groups | Excel Discussion (Misc queries) | |||
Problem with date base units for x axis | Charts and Charting in Excel | |||
Hyperlink to word document problem | Links and Linking in Excel | |||
Paper Tray selection Problem, | Excel Discussion (Misc queries) | |||
File is locked for Editing by user problem | Excel Discussion (Misc queries) |