Selective concatonate

[John Michl]

I have a table with that includes a dozen columns which have a simple "x" or
null value. I want to create a string that concatonate the column headers
for columns that include a yes value. Here is a simple example.

Row A Apples Pears Grape Concatonated
String
John x x
Apples Pears
Sue x x
Apples Pears Grape
Peter x
Pears

What formula would I use to create the concatonated string? Note: I can
substitute a different value or string for the "x". Ideally, a comma would
be used between words in the list.

Thanks!

- John

 

[Frank Kabel]

Hi
do you have only these 3 columns or are there more?. If you have
several columns a VBA solution would be the way to go

 

[John Michl]

Ten columns and 1300 records.

- John

Hi
do you have only these 3 columns or are there more?. If you have
several columns a VBA solution would be the way to go

 

[Frank Kabel]

Hi
try the following:
1. Download the free add-in Morefunc.xll and install it
http://longre.free.fr/english/

2. Assuming that your 'X' are in columns B:K (starzing in row 2) enter
the following array formula in L2 /(entered with CTRL+SHIFT+ENTER):
=MID(SUBSTITUTE(", " & MCONCAT(IF(B2:K2="X",$B$1:$K$1,"^"),", "),",
^",""),3,1024)
and copy down