transpose array of non-adjacent cells
 

I have a range of cells in a single row. This range is C3:AD3. I am trying to
transpose this specific cells from that range into a single column, B34:B43.

In effect, it would look like this:

=TRANSPOSE({$C$3,$F$3,$I$3,$L$3,$O$3,$R$3,$U$3,$X$ 3,$AA$3,$AD$3})

Furthermore, I want to sort this list, so it would look like this:

=SORT(TRANSPOSE({$C$3,$F$3,$I$3,$L$3,$O$3,$R$3,$U$ 3,$X$3,$AA$3,$AD$3}))

Neither of these functions work, and it seems the culprit is that Excel 2007
doesn't want to support non-adjacent cells in an array. How can I get around
this without the use of some ridiculous macro? I just want it to try a group
of non-adjacent cells as a single array.

transpose array of non-adjacent cells
 

Enter this in B34 and copy down:

=INDEX(C$3:AD$3,3*ROWS($1:1)-2)
--
HTH,

RD

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

"ignite ice" <ignite wrote in message
...
I have a range of cells in a single row. This range is C3:AD3. I am trying
to
transpose this specific cells from that range into a single column,
B34:B43.

In effect, it would look like this:

=TRANSPOSE({$C$3,$F$3,$I$3,$L$3,$O$3,$R$3,$U$3,$X$ 3,$AA$3,$AD$3})

Furthermore, I want to sort this list, so it would look like this:

=SORT(TRANSPOSE({$C$3,$F$3,$I$3,$L$3,$O$3,$R$3,$U$ 3,$X$3,$AA$3,$AD$3}))

Neither of these functions work, and it seems the culprit is that Excel
2007
doesn't want to support non-adjacent cells in an array. How can I get
around
this without the use of some ridiculous macro? I just want it to try a
group
of non-adjacent cells as a single array.

transpose array of non-adjacent cells
 

This formula works, but it does not sort the list, nor can I sort the list
using Sort & Filter. That is one thing I need to be able to do.

"Ragdyer" wrote:

Enter this in B34 and copy down:

=INDEX(C$3:AD$3,3*ROWS($1:1)-2)
--
HTH,

RD

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

"ignite ice" <ignite wrote in message
...
I have a range of cells in a single row. This range is C3:AD3. I am trying
to
transpose this specific cells from that range into a single column,
B34:B43.

In effect, it would look like this:

=TRANSPOSE({$C$3,$F$3,$I$3,$L$3,$O$3,$R$3,$U$3,$X$ 3,$AA$3,$AD$3})

Furthermore, I want to sort this list, so it would look like this:

=SORT(TRANSPOSE({$C$3,$F$3,$I$3,$L$3,$O$3,$R$3,$U$ 3,$X$3,$AA$3,$AD$3}))

Neither of these functions work, and it seems the culprit is that Excel
2007
doesn't want to support non-adjacent cells in an array. How can I get
around
this without the use of some ridiculous macro? I just want it to try a
group
of non-adjacent cells as a single array.

transpose array of non-adjacent cells
 

Enter this in C34 and copy down:

=SMALL(B$34:B$43,ROWS($1:1))
--
HTH,

RD

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

"ignite ice" wrote in message
...
This formula works, but it does not sort the list, nor can I sort the list
using Sort & Filter. That is one thing I need to be able to do.

"Ragdyer" wrote:

Enter this in B34 and copy down:

=INDEX(C$3:AD$3,3*ROWS($1:1)-2)
--
HTH,

RD


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

--------------------------------------------------------------------------
-

"ignite ice" <ignite wrote in message
...
I have a range of cells in a single row. This range is C3:AD3. I am
trying
to
transpose this specific cells from that range into a single column,
B34:B43.

In effect, it would look like this:

=TRANSPOSE({$C$3,$F$3,$I$3,$L$3,$O$3,$R$3,$U$3,$X$ 3,$AA$3,$AD$3})

Furthermore, I want to sort this list, so it would look like this:


=SORT(TRANSPOSE({$C$3,$F$3,$I$3,$L$3,$O$3,$R$3,$U$ 3,$X$3,$AA$3,$AD$3}))

Neither of these functions work, and it seems the culprit is that
Excel
2007
doesn't want to support non-adjacent cells in an array. How can I get
around
this without the use of some ridiculous macro? I just want it to try a
group
of non-adjacent cells as a single array.