Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate function should accept cell-ranges
Why should a user have to specify:
=Concatenate(A1, A2, A3, B1, B2, B3) ? Why not simply: =Concatenate(A1:B3) ? This would make it *so* much easier to abstract away certain changes, to reverse operations such as "text-to-columns", and to avoid making errors. I've had to concatenate a series of more than 20 cells before, and it really gets tedious after only the 3rd one. If I had to insert a column or row to the concatenation, I had to update the function again, and if I made a mistake it was hard to detect, etc etc. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...lic.excel.misc |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate function should accept cell-ranges
"johndog" wrote in message
... Why should a user have to specify: =Concatenate(A1, A2, A3, B1, B2, B3) ? Why not simply: =Concatenate(A1:B3) ? This would make it *so* much easier to abstract away certain changes, to reverse operations such as "text-to-columns", and to avoid making errors. I've had to concatenate a series of more than 20 cells before, and it really gets tedious after only the 3rd one. If I had to insert a column or row to the concatenation, I had to update the function again, and if I made a mistake it was hard to detect, etc etc. Are you happy for Excel to guess whether when you said (A1:B3) you intended (A1, A2, A3, B1, B2, B3) or (A1, B1, A2, B2, A3, B3)? -- David Biddulph |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate function should accept cell-ranges
"David Biddulph" wrote: "johndog" wrote in message ... Why should a user have to specify: =Concatenate(A1, A2, A3, B1, B2, B3) ? Why not simply: =Concatenate(A1:B3) ? This would make it *so* much easier to abstract away certain changes, to reverse operations such as "text-to-columns", and to avoid making errors. I've had to concatenate a series of more than 20 cells before, and it really gets tedious after only the 3rd one. If I had to insert a column or row to the concatenation, I had to update the function again, and if I made a mistake it was hard to detect, etc etc. Are you happy for Excel to guess whether when you said (A1:B3) you intended (A1, A2, A3, B1, B2, B3) or (A1, B1, A2, B2, A3, B3)? -- David Biddulph Good rhetorical question. Obviously the answer is no. But Excel doesn't need to guess, it can simply lay down some rules. These are all options, for instance: 1) One row or one column only 2) Left to right, top down only 3) Allow a parameter to select between left-right-top-down, right-left-top-down, bottum-up-right-left, bottum-up-left-right (though this would probably need another function altogether) Dependency on ordering of cells in ranges is certainly precedented. For instance, many of the search functions will assume the range is sorted. But in this case, the behavior I'm suggesting is not required, and if the user needs to combine their cells in a specific non-orderly way, then ranges probably aren't an interesting solution to their problem. They can simply continue to do it the old way. However, in the common "inverse of text to columns" scenario discussed in the Excel's help, the issue of guessing which order excel will do things in shouldn't even come into question; the output should simply be the most intuitive, which is to say that it should loosely correspond to the opposite behavior of "text to columns", whose behavior is also intuitive. Combining rows *and* columns goes beyond "opposite behavior" of text-to-columns, but it could be an added benefit. I actually think a fixed "left-right-top-down" behavior is rather reasonable. Geometrically, left-to-right maps in order into the output string, and since the output string reads left-right, it follows that top-to-bottom ordering would get a lower priority. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate function should accept cell-ranges
If the range was restricted to a 1-D array, then the user could
specify: =CONCATENATE(A1:A3,B1:B3) or =CONCATENATE(A1:B1,A2:B2,A3:B3) though there is not much benefit in the second one over what we currently have. Often, though, when you are recombining columns, you want to put some delimiter between them, such as: =A1&","&A2&","&A3 etc., so there wouldn't be much advantage in accepting cell ranges in this instance. Pete johndog wrote: "David Biddulph" wrote: "johndog" wrote in message ... Why should a user have to specify: =Concatenate(A1, A2, A3, B1, B2, B3) ? Why not simply: =Concatenate(A1:B3) ? This would make it *so* much easier to abstract away certain changes, to reverse operations such as "text-to-columns", and to avoid making errors. I've had to concatenate a series of more than 20 cells before, and it really gets tedious after only the 3rd one. If I had to insert a column or row to the concatenation, I had to update the function again, and if I made a mistake it was hard to detect, etc etc. Are you happy for Excel to guess whether when you said (A1:B3) you intended (A1, A2, A3, B1, B2, B3) or (A1, B1, A2, B2, A3, B3)? -- David Biddulph Good rhetorical question. Obviously the answer is no. But Excel doesn't need to guess, it can simply lay down some rules. These are all options, for instance: 1) One row or one column only 2) Left to right, top down only 3) Allow a parameter to select between left-right-top-down, right-left-top-down, bottum-up-right-left, bottum-up-left-right (though this would probably need another function altogether) Dependency on ordering of cells in ranges is certainly precedented. For instance, many of the search functions will assume the range is sorted. But in this case, the behavior I'm suggesting is not required, and if the user needs to combine their cells in a specific non-orderly way, then ranges probably aren't an interesting solution to their problem. They can simply continue to do it the old way. However, in the common "inverse of text to columns" scenario discussed in the Excel's help, the issue of guessing which order excel will do things in shouldn't even come into question; the output should simply be the most intuitive, which is to say that it should loosely correspond to the opposite behavior of "text to columns", whose behavior is also intuitive. Combining rows *and* columns goes beyond "opposite behavior" of text-to-columns, but it could be an added benefit. I actually think a fixed "left-right-top-down" behavior is rather reasonable. Geometrically, left-to-right maps in order into the output string, and since the output string reads left-right, it follows that top-to-bottom ordering would get a lower priority. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
use formatting from source cell in CONCATENATE function | Excel Worksheet Functions | |||
CELL Function: cell reference by formula | Excel Worksheet Functions | |||
numerical integration | Excel Discussion (Misc queries) | |||
copying the function contained within a cell to anouther cell. | Excel Worksheet Functions | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions |