Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transpose array sum | Excel Worksheet Functions | |||
transpose - array problem | Excel Discussion (Misc queries) | |||
Transpose a text string while copying adjacent column data to new | Excel Worksheet Functions | |||
Transpose Array | Excel Discussion (Misc queries) | |||
Conditional transpose to Array | Excel Discussion (Misc queries) |