Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
array transpose for dynamic data without macros
I'm having a problem with array functions and transpose.
I currently have a column of data that is unknown in length (it will be different every time it is used), and I would like to auto-populate column headers in a different sheet with this data. ATM I am using the following: =TRANSPOSE(OFFSET(<first_column_element,0,0,COUNT A(<column)-1,1)) My problem is that, since I have no idea how many items there will be, I have to apply this array formula to the entire span of columns which makes for a lot of #N/A columns. I've *fixed* this by conditionally formatting the font color to be the same as the cell background so the #N/A is not visible. I'd really like to make this array have a dynamic size based upon COUNTA() so I don't have to use my font color workaround, but I have no idea how to do this (or if it is even possible) without macros. I need to avoid macros if possible since I don't know if my users have the ability to allow macros to run. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
array transpose for dynamic data without macros
Here's one way, but it's different from the approaches you mentioned.
In the left-most column header, put =IF(OFFSET(Sheet1!$A$1,COLUMN()-2,0)="","", OFFSET(Sheet1!$A$1,COLUMN()-2,0)) but don't use 2; instead, use whatever number makes the OFFSET point to <first_column_element. If <first_column_element isn't in Sheet1 column A, change those parts too. Then select the cell containing the formula and drag rightward for the maximum length the column can ever have. Because of the IF test, a header cell should be simply empty if the corresponding column element is absent. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
array transpose for dynamic data without macros
Perhaps something simpler would suffice?
Assume your source data in Sheet1's col A, in A1 down In Sheet2, In A1: =INDEX(Sheet1!$A:$A,COLUMNS($A:A)) Copy across to cover the max expected extent of source data. That should return it transposed as desired. "Excess" fills will populate as zeros, which you can easily suppress visually in that sheet via ToolsOptionsView tab (uncheck Zero values). voila? celebrate it, hit the YES below -- Max Singapore --- "Rad131304" wrote: I'm having a problem with array functions and transpose. I currently have a column of data that is unknown in length (it will be different every time it is used), and I would like to auto-populate column headers in a different sheet with this data. ATM I am using the following: =TRANSPOSE(OFFSET(<first_column_element,0,0,COUNT A(<column)-1,1)) My problem is that, since I have no idea how many items there will be, I have to apply this array formula to the entire span of columns which makes for a lot of #N/A columns. I've *fixed* this by conditionally formatting the font color to be the same as the cell background so the #N/A is not visible. I'd really like to make this array have a dynamic size based upon COUNTA() so I don't have to use my font color workaround, but I have no idea how to do this (or if it is even possible) without macros. I need to avoid macros if possible since I don't know if my users have the ability to allow macros to run. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
array transpose for dynamic data without macros
Thanks, this worked!
"zvkmpw" wrote: Here's one way, but it's different from the approaches you mentioned. In the left-most column header, put =IF(OFFSET(Sheet1!$A$1,COLUMN()-2,0)="","", OFFSET(Sheet1!$A$1,COLUMN()-2,0)) but don't use 2; instead, use whatever number makes the OFFSET point to <first_column_element. If <first_column_element isn't in Sheet1 column A, change those parts too. Then select the cell containing the formula and drag rightward for the maximum length the column can ever have. Because of the IF test, a header cell should be simply empty if the corresponding column element is absent. . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
array transpose for dynamic data without macros
This seems to return the first value in the Index range $A:$A for all cells
where the formula is pasted. "Max" wrote: Perhaps something simpler would suffice? Assume your source data in Sheet1's col A, in A1 down In Sheet2, In A1: =INDEX(Sheet1!$A:$A,COLUMNS($A:A)) Copy across to cover the max expected extent of source data. That should return it transposed as desired. "Excess" fills will populate as zeros, which you can easily suppress visually in that sheet via ToolsOptionsView tab (uncheck Zero values). voila? celebrate it, hit the YES below -- Max Singapore --- "Rad131304" wrote: I'm having a problem with array functions and transpose. I currently have a column of data that is unknown in length (it will be different every time it is used), and I would like to auto-populate column headers in a different sheet with this data. ATM I am using the following: =TRANSPOSE(OFFSET(<first_column_element,0,0,COUNT A(<column)-1,1)) My problem is that, since I have no idea how many items there will be, I have to apply this array formula to the entire span of columns which makes for a lot of #N/A columns. I've *fixed* this by conditionally formatting the font color to be the same as the cell background so the #N/A is not visible. I'd really like to make this array have a dynamic size based upon COUNTA() so I don't have to use my font color workaround, but I have no idea how to do this (or if it is even possible) without macros. I need to avoid macros if possible since I don't know if my users have the ability to allow macros to run. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
array transpose for dynamic data without macros
Are you sure? It works darned well for me in testing here, and it should just
as well for you, too. I'm not sure what could possibly have happened over there when you tried it. But as it can be easily re-tested, re-read my steps, then give it another go. Just copy n paste the expression direct from my posting into Sheet2's A1, then drag A1 to copy ACROSS. Do NOT retype the expression, you may introduce inadvertent typo(s). For eg note that it is COLUMNS (with an S). Let me know here your results. -- Max Singapore --- "Rad131304" wrote: This seems to return the first value in the Index range $A:$A for all cells where the formula is pasted. "Max" wrote: Perhaps something simpler would suffice? Assume your source data in Sheet1's col A, in A1 down In Sheet2, In A1: =INDEX(Sheet1!$A:$A,COLUMNS($A:A)) Copy across to cover the max expected extent of source data. That should return it transposed as desired. "Excess" fills will populate as zeros, which you can easily suppress visually in that sheet via ToolsOptionsView tab (uncheck Zero values). voila? celebrate it, hit the YES below |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
array transpose for dynamic data without macros
I mis-typed it, i used: =INDEX(Sheet1!$A:$A,COLUMNS($A:$A))
instead of: =INDEX(Sheet1!$A:$A,COLUMNS($A:A)) On a side note, do you know if this introduces memory usage issues over some other way of doing it in an array function? I'm doing this in several sheets, and since it spans something like 16K+ columns in each sheet in 2K7, I think it's causing memory usage issues (~1GB of Memory for one XLTX file). "Max" wrote: Are you sure? It works darned well for me in testing here, and it should just as well for you, too. I'm not sure what could possibly have happened over there when you tried it. But as it can be easily re-tested, re-read my steps, then give it another go. Just copy n paste the expression direct from my posting into Sheet2's A1, then drag A1 to copy ACROSS. Do NOT retype the expression, you may introduce inadvertent typo(s). For eg note that it is COLUMNS (with an S). Let me know here your results. -- Max Singapore --- "Rad131304" wrote: This seems to return the first value in the Index range $A:$A for all cells where the formula is pasted. "Max" wrote: Perhaps something simpler would suffice? Assume your source data in Sheet1's col A, in A1 down In Sheet2, In A1: =INDEX(Sheet1!$A:$A,COLUMNS($A:A)) Copy across to cover the max expected extent of source data. That should return it transposed as desired. "Excess" fills will populate as zeros, which you can easily suppress visually in that sheet via ToolsOptionsView tab (uncheck Zero values). voila? celebrate it, hit the YES below |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
array transpose for dynamic data without macros
There you go. Glad you got it working
Start a new thread for your new query. I don't use xl07, but you can always use manual calc mode to control calcs only when needed. That simple way should work in any version. -- Max Singapore "Rad131304" wrote in message ... I mis-typed it, i used: =INDEX(Sheet1!$A:$A,COLUMNS($A:$A)) instead of: =INDEX(Sheet1!$A:$A,COLUMNS($A:A)) On a side note, do you know if this introduces memory usage issues over some other way of doing it in an array function? I'm doing this in several sheets, and since it spans something like 16K+ columns in each sheet in 2K7, I think it's causing memory usage issues (~1GB of Memory for one XLTX file). |
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 Array | Excel Discussion (Misc queries) | |||
dynamic transpose | Excel Discussion (Misc queries) | |||
Conditional transpose to Array | Excel Discussion (Misc queries) |