transpose a range to a single column

Is there a nifty neato way to put a range of values into a single column without a lot of manual
cutting and pasting and without VBA programming?

For example, if I have 5 rows of numbers in columns A, B, C, D, can I select the range B1:D5 and
dump all 15 numbers into column A? The TRANSPOSE function doesn't work for this type of range.
Thanks...

Jeff

There is no "automatic" way, without using VBA........you can just do Copy >
PasteSpecial > Transpose on one row at a time..........if there's only five
rows, it won't take all day.........

Vaya con Dios,
Chuck, CABGx3



"Jeff" <eatmy@grits.com> wrote in message
news:#u3ZnqMjGHA.1260@TK2MSFTNGP05.phx.gbl...
> Is there a nifty neato way to put a range of values into a single column
without a lot of manual
> cutting and pasting and without VBA programming?
>
> For example, if I have 5 rows of numbers in columns A, B, C, D, can I
select the range B1:D5 and
> dump all 15 numbers into column A? The TRANSPOSE function doesn't work
for this type of range.
> Thanks...
>
> Jeff
>
>

"CLR" <croberts@tampabay.rr.com> wrote in message news:eDoTs7MjGHA.4284@TK2MSFTNGP05.phx.gbl...
> There is no "automatic" way, without using VBA........you can just do Copy >
> PasteSpecial > Transpose on one row at a time..........if there's only five
> rows, it won't take all day.........

Ya, but doing it manually won't impress my girlfriend.

You could enter this formula in A6,
And copy down to A20,
And it will return the contents of the cells of B1 to D5:

=INDEX($B$1:$D$5,MOD(ROWS($1:1)-1,5)+1,ROWS($1:5)/5)

Actually, you could enter this formula *anywhere*,
And copying it down,
will return the contents of the cells, starting in B1 out to D5.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



"Jeff" <eatmy@grits.com> wrote in message
news:%23u3ZnqMjGHA.1260@TK2MSFTNGP05.phx.gbl...
Is there a nifty neato way to put a range of values into a single column
without a lot of manual
cutting and pasting and without VBA programming?

For example, if I have 5 rows of numbers in columns A, B, C, D, can I select
the range B1:D5 and
dump all 15 numbers into column A? The TRANSPOSE function doesn't work for
this type of range.
Thanks...

Jeff

I have a dynamic input range and would like to replace the 5 in your
formula with
ROWS(MyArray), but that don't work.
Manfred

RagDyeR wrote:
> You could enter this formula in A6,
> And copy down to A20,
> And it will return the contents of the cells of B1 to D5:
>
> =INDEX($B$1:$D$5,MOD(ROWS($1:1)-1,5)+1,ROWS($1:5)/5)
>
> Actually, you could enter this formula *anywhere*,
> And copying it down,
> will return the contents of the cells, starting in B1 out to D5.
> --
>
> HTH,
>
> RD
> =====================================================
> Please keep all correspondence within the Group, so all may benefit!
> =====================================================
>
>
>
> "Jeff" <eatmy@grits.com> wrote in message
> news:%23u3ZnqMjGHA.1260@TK2MSFTNGP05.phx.gbl...
> Is there a nifty neato way to put a range of values into a single column
> without a lot of manual
> cutting and pasting and without VBA programming?
>
> For example, if I have 5 rows of numbers in columns A, B, C, D, can I select
> the range B1:D5 and
> dump all 15 numbers into column A? The TRANSPOSE function doesn't work for
> this type of range.
> Thanks...
>
> Jeff

Could you be more specific?

Dynamic in which dimension?

Rows or Columns?
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Manfred" <manfredstraub@yahoo.com> wrote in message
news:1150202535.996055.94930@f14g2000cwb.googlegroups.com...
I have a dynamic input range and would like to replace the 5 in your
formula with
ROWS(MyArray), but that don't work.
Manfred

RagDyeR wrote:
> You could enter this formula in A6,
> And copy down to A20,
> And it will return the contents of the cells of B1 to D5:
>
> =INDEX($B$1:$D$5,MOD(ROWS($1:1)-1,5)+1,ROWS($1:5)/5)
>
> Actually, you could enter this formula *anywhere*,
> And copying it down,
> will return the contents of the cells, starting in B1 out to D5.
> --
>
> HTH,
>
> RD
> =====================================================
> Please keep all correspondence within the Group, so all may benefit!
> =====================================================
>
>
>
> "Jeff" <eatmy@grits.com> wrote in message
> news:%23u3ZnqMjGHA.1260@TK2MSFTNGP05.phx.gbl...
> Is there a nifty neato way to put a range of values into a single column
> without a lot of manual
> cutting and pasting and without VBA programming?
>
> For example, if I have 5 rows of numbers in columns A, B, C, D, can I
select
> the range B1:D5 and
> dump all 15 numbers into column A? The TRANSPOSE function doesn't work
for
> this type of range.
> Thanks...
>
> Jeff

UDF, if you like?
1) hit Alt + F11 to open vb Editor
2) go to [Insert] -> [Module] Then paste the code onto the blank space in the right pane
3) click x to close the window to get back to excel

select the number of cells (15 in your case) vertically and enter

=TransP(B1:D5)

and confirm with Ctrl + Shift + Enter (array forumla)

Function TransP(rng As Range)
Dim r As Range, a(), i As Long
Redim a(1 To rng.Count, 1 To 1)
For Each r In rng
     i = i + 1
     a(i, 1) = r.Value
     If r.HasFormula Then a(i, 1) = r.Formula
Next
TransP = a
End Function

My array is dynamic in the sense that Debra Dalgleish explains at
http://www.contextures.com/xlNames01.html

It can be *any size*, *anywhere*.
The only constant is the name MyArray.
Or in less technical terms, I am too lazy to count the rows and columns
of the array and put those numbers into the formula.
I just want to use the name MyArray.

I don't think I can be more specific. Put on your thinking cap.
Manfred


RagDyeR wrote:
> Could you be more specific?
>
> Dynamic in which dimension?
>
> Rows or Columns?
> --
>
> Regards,
>
> RD
> ----------------------------------------------------------------------------
> -------------------
> Please keep all correspondence within the Group, so all may benefit !
> ----------------------------------------------------------------------------
> -------------------
>
> "Manfred" <manfredstraub@yahoo.com> wrote in message
> news:1150202535.996055.94930@f14g2000cwb.googlegroups.com...
> I have a dynamic input range and would like to replace the 5 in your
> formula with
> ROWS(MyArray), but that don't work.
> Manfred
>
> RagDyeR wrote:
> > You could enter this formula in A6,
> > And copy down to A20,
> > And it will return the contents of the cells of B1 to D5:
> >
> > =INDEX($B$1:$D$5,MOD(ROWS($1:1)-1,5)+1,ROWS($1:5)/5)
> >
> > Actually, you could enter this formula *anywhere*,
> > And copying it down,
> > will return the contents of the cells, starting in B1 out to D5.
> > --
> >
> > HTH,
> >
> > RD
> > =====================================================
> > Please keep all correspondence within the Group, so all may benefit!
> > =====================================================
> >
> >
> >
> > "Jeff" <eatmy@grits.com> wrote in message
> > news:%23u3ZnqMjGHA.1260@TK2MSFTNGP05.phx.gbl...
> > Is there a nifty neato way to put a range of values into a single column
> > without a lot of manual
> > cutting and pasting and without VBA programming?
> >
> > For example, if I have 5 rows of numbers in columns A, B, C, D, can I
> select
> > the range B1:D5 and
> > dump all 15 numbers into column A? The TRANSPOSE function doesn't work
> for
> > this type of range.
> > Thanks...
> >
> > Jeff