Concatenate...If...Need to roll up text.

[Chris]

Hey there. I am wondering if what I need to do can be done with
functions. I know I could do it in VBA/Macro, but for reasons I do
not want to get into that is not an option at this time. Here's what
I want to do:

1 This
1 Is
2 Not
1 An
1 Option

I want to roll the text from column 2 into another cell based on the
value of column 1 being equal to "1" - thus the text should read "This
is an option".

Any ideas?

 

[Frank Kabel]

Hi
not really possible without VBA as Excel's text functions do not
support this kind of array operations

 

[donc]

Hey there. I am wondering if what I need to do can be done with
functions. I know I could do it in VBA/Macro, but for reasons I do
not want to get into that is not an option at this time. Here's what
I want to do:

1 This
1 Is
2 Not
1 An
1 Option

I want to roll the text from column 2 into another cell based on the
value of column 1 being equal to "1" - thus the text should read "This
is an option".

Any ideas?

It seems to me that this can be done with nested IF statements.

Here is an simpler example:

1 This
1 Not
1 That

=IF($A$1=1,$B$1&IF($A$2=1,$B$2&IF($A$3=1,$B$3,"-"),","),"*")

The basic idea is to concatenate the fields according to col.A.
The char's - , * are used to indicate what happens when a field
is skipped.

With all 1's in col.A we get

ThisNotThat

If A3 (==That) is 2, we get

ThisNot-

This indicates that the formula works somewhat. But not if the other
A-fields are changed to 2. So I think the idea is valid, but the
formula is not correct.

It's late and I am out of thinking for one day Hope this gives you
an idea.

donc

 

[JE McGimpsey]

One could certainly create a brute force solution using functions:

=IF(A1=1,B1 & " ","") & IF(A2=1,B2 & " ","") & IF(A3=1,...

Otherwise, I believe you'll need to program a solution.

 

[Chris]

One could certainly create a brute force solution using functions:

=IF(A1=1,B1 & " ","") & IF(A2=1,B2 & " ","") & IF(A3=1,...

Otherwise, I believe you'll need to program a solution.

<snip>

Thanks all for your input. I was trying to avoid having to nest a
bunch of if statements, but it looks like it's that or I convince my
boss to go the VBA route and I'll write a roll-up function. Thanks
again!