| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is there an elegant way?
Hi All,
I am trying to write an automated commentary on some financial variances. From here on my methos so far and a better method may diverge. I have set up a grid where those variances I want to report are shown, e.g. blank Loans blank Deposits blank Cash blank blank Deposits Other blank blank Shares blank blank and I want the following displayed: Loans, Deposits; Cash, Deposits; Shares; EXACTLY like that ... (lining up on the same rows as the above is fine). I am not sure if this IS difficult or I am just MAKING this difficult! Really would appreciate any help as manually creating these commentaries is tedious. TIA and cheers, Matt Geare "veryeavy" on BBO :-) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is there an elegant way?
Do you want the results to appear in one column? If so, try this (assuming
data is in A1:E3) and copy down: =IF(COUNTA(A1:E1),SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1)," ",", ")&";","") If you want them in separate columns, use the formula above, then use Data/Text To Columns using Space as the delimiter. "veryeavy" wrote: Hi All, I am trying to write an automated commentary on some financial variances. From here on my methos so far and a better method may diverge. I have set up a grid where those variances I want to report are shown, e.g. blank Loans blank Deposits blank Cash blank blank Deposits Other blank blank Shares blank blank and I want the following displayed: Loans, Deposits; Cash, Deposits; Shares; EXACTLY like that ... (lining up on the same rows as the above is fine). I am not sure if this IS difficult or I am just MAKING this difficult! Really would appreciate any help as manually creating these commentaries is tedious. TIA and cheers, Matt Geare "veryeavy" on BBO :-) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is there an elegant way?
The NG wrapped the text so that you cannot see the space after the comma, so
I wrote it in. =IF(COUNTA(A1:E1),SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1)," ",",<space")&";","") "JMB" wrote: Do you want the results to appear in one column? If so, try this (assuming data is in A1:E3) and copy down: =IF(COUNTA(A1:E1),SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1)," ",", ")&";","") If you want them in separate columns, use the formula above, then use Data/Text To Columns using Space as the delimiter. "veryeavy" wrote: Hi All, I am trying to write an automated commentary on some financial variances. From here on my methos so far and a better method may diverge. I have set up a grid where those variances I want to report are shown, e.g. blank Loans blank Deposits blank Cash blank blank Deposits Other blank blank Shares blank blank and I want the following displayed: Loans, Deposits; Cash, Deposits; Shares; EXACTLY like that ... (lining up on the same rows as the above is fine). I am not sure if this IS difficult or I am just MAKING this difficult! Really would appreciate any help as manually creating these commentaries is tedious. TIA and cheers, Matt Geare "veryeavy" on BBO :-) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is there an elegant way?
Uber-elegant.
It would have taken me a month of Sundays and I still wouldn't have hit on this. Many Many Thanks JMB :-) "JMB" wrote: The NG wrapped the text so that you cannot see the space after the comma, so I wrote it in. =IF(COUNTA(A1:E1),SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1)," ",",<space")&";","") "JMB" wrote: Do you want the results to appear in one column? If so, try this (assuming data is in A1:E3) and copy down: =IF(COUNTA(A1:E1),SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1)," ",", ")&";","") If you want them in separate columns, use the formula above, then use Data/Text To Columns using Space as the delimiter. "veryeavy" wrote: Hi All, I am trying to write an automated commentary on some financial variances. From here on my methos so far and a better method may diverge. I have set up a grid where those variances I want to report are shown, e.g. blank Loans blank Deposits blank Cash blank blank Deposits Other blank blank Shares blank blank and I want the following displayed: Loans, Deposits; Cash, Deposits; Shares; EXACTLY like that ... (lining up on the same rows as the above is fine). I am not sure if this IS difficult or I am just MAKING this difficult! Really would appreciate any help as manually creating these commentaries is tedious. TIA and cheers, Matt Geare "veryeavy" on BBO :-) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is there an elegant way?
You are quite welcome.
"veryeavy" wrote: Uber-elegant. It would have taken me a month of Sundays and I still wouldn't have hit on this. Many Many Thanks JMB :-) "JMB" wrote: The NG wrapped the text so that you cannot see the space after the comma, so I wrote it in. =IF(COUNTA(A1:E1),SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1)," ",",<space")&";","") "JMB" wrote: Do you want the results to appear in one column? If so, try this (assuming data is in A1:E3) and copy down: =IF(COUNTA(A1:E1),SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1)," ",", ")&";","") If you want them in separate columns, use the formula above, then use Data/Text To Columns using Space as the delimiter. "veryeavy" wrote: Hi All, I am trying to write an automated commentary on some financial variances. From here on my methos so far and a better method may diverge. I have set up a grid where those variances I want to report are shown, e.g. blank Loans blank Deposits blank Cash blank blank Deposits Other blank blank Shares blank blank and I want the following displayed: Loans, Deposits; Cash, Deposits; Shares; EXACTLY like that ... (lining up on the same rows as the above is fine). I am not sure if this IS difficult or I am just MAKING this difficult! Really would appreciate any help as manually creating these commentaries is tedious. TIA and cheers, Matt Geare "veryeavy" on BBO :-) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is there an elegant way?
=SUBSTITUTE(TRIM(CONCATENATE(A1," ",B1," ",C1," ",D1," ",E1))&";"," ",", ")
"veryeavy" wrote: Hi All, I am trying to write an automated commentary on some financial variances. From here on my methos so far and a better method may diverge. I have set up a grid where those variances I want to report are shown, e.g. blank Loans blank Deposits blank Cash blank blank Deposits Other blank blank Shares blank blank and I want the following displayed: Loans, Deposits; Cash, Deposits; Shares; EXACTLY like that ... (lining up on the same rows as the above is fine). I am not sure if this IS difficult or I am just MAKING this difficult! Really would appreciate any help as manually creating these commentaries is tedious. TIA and cheers, Matt Geare "veryeavy" on BBO :-) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Is there an elegant solution to this table? | Excel Discussion (Misc queries) | |||
| Profitability Index--More Elegant Equation | Excel Discussion (Misc queries) | |||
| More elegant way to do IF () | Excel Worksheet Functions | |||
| Working Hours (formula & graph) - any elegant solution? | Excel Worksheet Functions | |||
| More elegant method? | Excel Worksheet Functions | |||
Linear Mode
