Creating a long string of text...

A

AlieSlavin

Hi there,

I'm trying to figure out a better way to do this - since the formula is
already too long for the cell (and I don't want to split it between two
cells, I want to find a better, more automatic way.

So this is my formula (same thing repeated with more cells)

=IF(BK$6="DE",($B$6 & ", "),"")&(IF(BK$7="DE",$B$7 & ",
",""))&(IF(BK$8="DE",$B$8 & ", ",""))&(IF(BK$9="DE",$B$9 & ",
",""))&(IF(BK$10="DE",$B$10 & ", ",""))....etc.

I've looked at Lookup, but it doesn't seem to allow for finding numerous
occurances and listing them out. I could be mistaken though.

Please help!

Thank you,
Alison
 
D

Don Guillett

try this where you look for the FIRST match of de in the bk column and index
the b column

=INDEX($B$6:$B$10,MATCH("de",$BK6:$BK$10,-1))&","
 
A

AlieSlavin

That returned just the first match, which is great, but I need all of the
matches.

It's so annoying because I know how to do this in VBA script by looping it,
but I want this to be via Worksheet Function.
 
H

Harlan Grove

AlieSlavin said:
I'm trying to figure out a better way to do this - since the
formula is already too long for the cell (and I don't want to
split it between two cells, I want to find a better, more
automatic way.

So this is my formula (same thing repeated with more cells)

=IF(BK$6="DE",($B$6 & ", "),"")
&(IF(BK$7="DE",$B$7 & ", ",""))
&(IF(BK$8="DE",$B$8 & ", ",""))
&(IF(BK$9="DE",$B$9 & ", ",""))
&(IF(BK$10="DE",$B$10 & ", ",""))....etc.

I've looked at Lookup, but it doesn't seem to allow for finding
numerous occurances and listing them out. I could be mistaken
though.
Click to expand...

Excel doesn't provide a built-in function for generalized
concatenation. You could try Laurent Longre's MOREFUNC.XLL add-in,
from http://xcell05.free.fr/english/, which provides an add-in
function named MCONCAT, which you could use in an array formula like

=MCONCAT(IF($BK$6:$BK$1000="DE",$B$6:$B$1000&", ",""))

The problem with MCONCAT is that it's subject to the XLL interface's
inability to return strings longer than 255 characters. If your
concatenated string could exceed 255 characters, you'd need to use
VBA. One possibility may be found in the following article from the
Google Groups archive.

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/94456a9e326b19a6

(or http://tinyurl.com/2go6pf ).
 
H

Harlan Grove

AlieSlavin said:
That returned just the first match, which is great, but I need all
of the matches.

It's so annoying because I know how to do this in VBA script by
looping it, but I want this to be via Worksheet Function.
Click to expand...
....

Then if you have, say, 1000 cells you need to check in col BK, so you
might need to concatenate up to 1000 cells in col B, the easiest
approach would be to use 1000 other cells to generate the result.

IV6:
=IF($BK$6="DE",$B$6&", ","")

IV7:
=IV6&IF($BK$7="DE",$B$7&", ","")

Fill IV7 down into IV8:IV1005. Cell IV1005 would evaluate to what you
want. If you want to use fewer cells, put more of the IF calls into
each cell. Excel doesn't make this easy to do with formulas.
 
A

AlieSlavin

MCONCAT was great, but it wouldn't work the if. So I made a silly, but
functional work around using it.

I created another sheet to match my 1st one. Only in the cells that would
have contained "DE" I put in
=IF(Sheet1!BJ5="DE",($B5 &", "),"")

Then on the main sheet used
=MCONCAT(DE!BJ5:BJ37)

Worked like a charm. :)

Thank you to everyone for your help!!
 
Top