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

  • Thread starter Thread starter Chris
  • Start date Start date
C

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?
 
Hi
not really possible without VBA as Excel's text functions do not
support this kind of array operations
 
Chris said:
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?
Click to expand...

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
 
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.
 
JE McGimpsey said:
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.
Click to expand...
<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!
 
Back
Top