Evaluate non-blank, substitute and combine

Hi, this might not be the best solution but it at least get rid of the last comma and shall be less painful when expanding to more columns

Formula:

Please Login or Register  to view this content.

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Or this

=SUBSTITUTE(TRIM(IFERROR(INDEX(B$5,MATCH("x",B6)),"")&" "&IFERROR(INDEX(C$5,MATCH("x",C6)),"")&" "&IFERROR(INDEX(D$5,MATCH("x",D6)),""))," ",", ")

or even this (shorter version)


=SUBSTITUTE(TRIM(IF(B$6:B$11="X",B$5,"")&" "&IF(C$6:C$11="X",C$5,"")&" "&IF(D$6:D$11="X",D$5,""))," ",", ")

Here's a method that uses a user defined function.

With your file open...

Press the key combination of Alt+F11 to open the Visual Basic Editor

Goto the menu Insert > Module

Copy the VBA code at the link below and paste it into the window that opens on the right side of the screen:

http://www.excelforum.com/showthread...=1#post3096647

Press the key combination of ALT+Q to close the Visual Basic Editor and return to Excel.

Data Range

	A	B	C	D	E
5	Customer	Apple	Orange	Banana	Fruits Used
6	Customer 1	x	x	x	Apple, Orange, Banana
7	Customer 2			x	Banana
8	Customer 3		x	x	Orange, Banana
9	Customer 4	x		x	Apple, Banana
10	Customer 5		x		Orange
11	Customer 6	x			Apple

Enter this array formula** in E6 and copy down as needed:

=concatall(IF(B6:D6="x",B$5:D$5,""),", ")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

You'll have to save the file as a macro enabled file in the *.xlsm file format.

You're welcome. We appreciate the feedback!