transpose a range to a single column

[Jeff]

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 B15 and
dump all 15 numbers into column A? The TRANSPOSE function doesn't work for this type of range.
Thanks...

Jeff

 

[CLR]

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

 

[RagDyeR]

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!
=====================================================



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 B15 and
dump all 15 numbers into column A? The TRANSPOSE function doesn't work for
this type of range.
Thanks...

Jeff

 

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

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

 

[Manfred]

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]

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

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

 

[jindon]

UDF, if you like?
1) hit Alt + F11 to open vb Editor
2) go to [Insert] -> [Module] Then paste the code onto the blank spac
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(B15)

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


Code
-------------------

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 Functio

 

[Manfred]

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