Is there an elegant way?

[Guest]

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

 

[Guest]

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.

 

[Guest]

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>")&";","")

 

[Guest]

Uber-elegant.

It would have taken me a month of Sundays and I still wouldn't have hit on
this.

Many Many Thanks JMB

 

[Guest]

You are quite welcome.

Uber-elegant.

It would have taken me a month of Sundays and I still wouldn't have hit on
this.

Many Many Thanks JMB

 

[Guest]

=SUBSTITUTE(TRIM(CONCATENATE(A1," ",B1," ",C1," ",D1," ",E1))&";"," ",", ")