A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

transpose array of non-adjacent cells



 
 
Thread Tools Display Modes
  #1  
Old June 18th, 2009, 02:29 AM posted to microsoft.public.excel.worksheet.functions
ignite ice
external usenet poster
 
Posts: 1
Default 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.
Ads
  #2  
Old June 18th, 2009, 03:50 AM posted to microsoft.public.excel.worksheet.functions
RagDyeR
external usenet poster
 
Posts: 3,482
Default 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.


  #3  
Old June 18th, 2009, 04:39 AM posted to microsoft.public.excel.worksheet.functions
ignite ice[_2_]
external usenet poster
 
Posts: 1
Default 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.



  #4  
Old June 18th, 2009, 09:58 AM posted to microsoft.public.excel.worksheet.functions
RagDyeR
external usenet poster
 
Posts: 3,482
Default 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
news
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.




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:05 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.