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.

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.
>.
>

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

All you have to do is select the data, Edit/Copy, then click in a blank area and do Edit/Paste Special, check "Transpose", OK. Then cut and paste the transposed data back where you want it. (Excel won't let you transpose in place. In '97, any way.)

Steve

just re-read your problem..never mind

In cell B1, enter

=INDEX($C$1:$L$4,FLOOR(ROW()/10.1,1)+1,ROW()-FLOOR(ROW()/10.1,1)*10)

drag down to B40

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

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.
> >.
> >
>

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
>